Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, new to DAX and Powerbi,
I have two tables in a 1:M relationship:
TableA (1)
- GUID (key)
- Name
TableB: (M)
- ID (key)
- GUID
- TYPE
Where type can be 1 or 2
I am trying to develop measures to calcaulate Types: "1 only", "2 only" or a "1 AND 2"
I have used the following measures to determine # of Type "1 only" (hopefully the values are correct), but struggling to get the "1 AND 2" to work.
TYPE1 ONLY:
CALCULATE( COUNTAX(TableB,TableB[GUID], DISTINCT(TableB[GUID]), FILTER(TableB,TableB[type]=1) )
I used the same formula above for 2 only by changing the the filter to Type = 2
However when trying to do this for Both, I get an Zero results (which I know to be incorrect) I was trying:
CALCULATE( COUNTAX(TableB,TableB[GUID], DISTINCT(TableB[GUID]), FILTER(TableB,TableB[type]=1),
FILTER(TableB,TableB[type]=2)
)
I have also tried combinations of FILTER as
FILTER(TableB,TableB[type]=1 && TableB[type]=2) which does not seem to work...
Any suggestion as to where I am maybe going wrong?
Thanks in advance.
Solved! Go to Solution.
OK, how about this
Count of Records = countrows(TableB)
Count of any type 1 = calculate([count of records],TableB[Type]="1")
Count of any type 2 = calculate([count of records],TableB[Type]="2")
Count only type 1 =SUMX(TableA,if([Count of any type 1] >0 && [Count of any type 2] = 0,1))
Count only type 2 =SUMX(TableA,if([Count of any type 1] =0 && [Count of any type 2] > 0,1))
Count always both =SUMX(TableA,if([Count of any type 1] >0 && [Count of any type 2] > 0,1))
Thanks, that seems to do the trick 🙂
Thanks, Sorry some context may help.
Table A is a lead table
Table B is a Cart table
Scenarios that can occur:
lead could have type 1 product
lead could have type 2 product (or mutiple of)
lead coud have type 1 and type 2 products
So I am trying to determine the number of leads with:
Type 1 products only
Type 2 products only
Both Type 1 and Typ2
Hope this clears things up.
OK, how about this
Count of Records = countrows(TableB)
Count of any type 1 = calculate([count of records],TableB[Type]="1")
Count of any type 2 = calculate([count of records],TableB[Type]="2")
Count only type 1 =SUMX(TableA,if([Count of any type 1] >0 && [Count of any type 2] = 0,1))
Count only type 2 =SUMX(TableA,if([Count of any type 1] =0 && [Count of any type 2] > 0,1))
Count always both =SUMX(TableA,if([Count of any type 1] >0 && [Count of any type 2] > 0,1))
I think you are making this harder than it needs to be :-). Try this
Count of Records = countrows(TableB)
This will count both types
Count of type 1 = calculate([count of records],TableB[Type]="1")
Count of type 2 = calculate([count of records],TableB[Type]="2")
If you want to learn quickly in a structured way, you may like to consider reading my book - I am sure it will help you
Have you tried removing both filters? I am not sure what your end goal / purpose is, but if you use:
Both = CALCULATE( COUNTAX(TableB,TableB[GUID]), DISTINCT(TableB[GUID]) )
If this works, please marked as accepted solution, if not please let me know what your anticipated values would be and we can work at getting there.
Thanks
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |