Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |