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,
I'm struggling with a really simple concept, and trying to get a measure which ignores initial filter context coming from a row in a table to correctly aggregate values. Screeshot below shoes the scenario. Basically want to go from table on the LHS to the table on the RHS. I understand why the Count becomes 1 for each MeetingID in the RHS table but am hitting a road block in getting the right DAX to give the 'Count Desired'. Any help much appreciated!
Solved! Go to Solution.
try:
Count Desired = IF( COUNTROWS(FactMeetings) >0, CALCULATE( COUNTROWS(FactMeetings), FILTER( ALL(FactMeetings), MAX( DimSalesePerson[Initials]) = FactMeetings[Initials] ) ) )
try:
Count Desired = IF( COUNTROWS(FactMeetings) >0, CALCULATE( COUNTROWS(FactMeetings), FILTER( ALL(FactMeetings), MAX( DimSalesePerson[Initials]) = FactMeetings[Initials] ) ) )
Thanks @Anonymous , that works great! It was the MAX() part I was missing to try and redefine the required context. Though I can see the effect of removing the IF > 0 wrap around, I guess I don't fully understand the theory as why without it the filter between the tables seems to be multiplying out the columns.
it has to do with :
FILTER( ALL(FactMeetings),
The way ALL is being used here is to remove filters. There's more too it, but there are many sources out there that have explained this. But just play around with ALL using a table and ALL using a Table Column, ALLEXCEPT. Just see what happens and it will begin to click.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |