Count only common

DAX DAX Calculations
1

Hello,

I have a question that I am probably overthinking about. I have a table that has IDs with pre and post data and I would only count them if they have appeared in both pre and post. How would I be doing that? I’m attaching a sample screenshot below.

image
image3024×4032 5.77 MB

I’m trying to show a distinct count of IDs that have appeared for both pre and post.

I tried to do a distinct count greater than one but that would not be a possibility since the ID can appear more than once for either pre/post depending on the number of days.

2

@supergallagher25 ,

A number of different ways you could do this, but here’s the approach I took using INTERSECT:

image

image
image771×793 32.5 KB

I hope this is helpful.

  • Brian
1 Like
3

Brian,

Thank you so much for your time and help. I haven’t had the chance to work with EVALUATE much so I might be doing something incorrectly here. I get an error when I use that formula.

I used the following formula upon seeing yours:
Counts =

EVALUATE

Var vTablePre = CALCULATETABLE(VALUES(tbl1[Id]), tbl1[att]= “Pretest”)

Var vTablePost = CALCULATETABLE(VALUES(tbl1[Id]), tbl1[att]= “Posttest”)

Var Result = INTERSECT(vTablePre,vTablePost)

Return Result

And this is the error,
image

4

Hi @supergallagher25. @BrianJ was using Tabular Editor for his example, which needs an EVALUATE to start. You don’t need (in fact, can’t use) EVALUATE within Power BI.
Greg

1 Like
5

@supergallagher25 ,

Sorry – I should’ve been clearer about that. What I provided was a DAX query, which returns a table and starts with EVALUATE. This is very useful for debugging and constructing measures/calculated tables, but as @Greg said, can’t be used directly in Power BI with the EVALUATE statement.

You have two choices,.

  1. You can create a calculated table using Modeling, New Table and then entering the code I provided without EVALUATE.

  2. Your second option is to use a measure, with one of my favorite cheats – CONCATENATEX combined with UNICHAR(10), which returns a scalar value that looks like a table.

image
image923×327 106 KB

If you want to do a bit of a deep dive into these topics, I cover all this and more in a recent video:

I hope this is helpful. Full solution file attached.

6

Hi @supergallagher25 , did the response provided by the users and experts help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

7

Hi @supergallagher25, we’ve noticed that no response has been received from you since July 13th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hi @supergallagher25, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.