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.
Tearing my hair out on this.
I have a table with many rows that when a distinct count is performed across the first pair of columns produces the result:
A|Z|15
A|Y|10
B|Y|1
A|X|6
A|W|10
B|W|2
ie. above, there are 15 record in the original table with A|Z|.
But i want to subtract the distinct count for 'B' from 'A'
Thus the |A|W| beciomes 8 rather than 10. See below.
A|Z|15
A|Y|9
B|Y|1
A|X|6
A|W|8
B|W|2
This needs to be done depending on slicers, so i cant do it with an excluding join in the SQL query.
Im not very good with DAX, and to date i have gotten by with doing this sort of thing in SQL queries.
Solved! Go to Solution.
Hi @al4x,
Based on my test, you should be able to use the formula below to create a new measure to subtract the distinct count for 'B' from 'A' in your scenario.
Measure = VAR currentColumn1 = FIRSTNONBLANK ( Table1[Column1], 1 ) VAR currentColumn2 = FIRSTNONBLANK ( Table1[Column2], 1 ) RETURN IF ( currentColumn1 = "A", DISTINCTCOUNT ( Table1[Column3] ) - CALCULATE ( DISTINCTCOUNT ( Table1[Column3] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Column1] = "B" && Table1[Column2] = currentColumn2 ) ), DISTINCTCOUNT ( Table1[Column3] ) )
Here is the sample pbix file for your reference.
Regards
Hi @al4x,
Based on my test, you should be able to use the formula below to create a new measure to subtract the distinct count for 'B' from 'A' in your scenario.
Measure = VAR currentColumn1 = FIRSTNONBLANK ( Table1[Column1], 1 ) VAR currentColumn2 = FIRSTNONBLANK ( Table1[Column2], 1 ) RETURN IF ( currentColumn1 = "A", DISTINCTCOUNT ( Table1[Column3] ) - CALCULATE ( DISTINCTCOUNT ( Table1[Column3] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Column1] = "B" && Table1[Column2] = currentColumn2 ) ), DISTINCTCOUNT ( Table1[Column3] ) )
Here is the sample pbix file for your reference.
Regards
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |