Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
My data looks something like this:
ID | Date | Service | Department | Pause |
1 | 01012022 | This | That | False |
1 | 02012022 | This | That | True |
2 | 01012022 | Other | Somewhere | True |
2 | 02012022 | Other | Somewhere | True |
3 | 01012022 | Dis | There | False |
3 | 02022022 | Dis | There | False |
I've made a matrix visual that has the first four columns as columns, week number as row, and for the value, I did this:
VAR __Active = CALCULATE(COUNTROWS(DISTINCT(TABLE[Date])))
var __Pause = CALCULATE(COUNTROWS(DISTINCT(TABLE[Pause])), TABLE[Pause] = FALSE())
RETURN
IF(__Pause = 1 && __Active > 0, 1)
1. There are only two dates I'm looking at - the table is filtered on monday and tuesday only
2. __Active will show me if at least one of the dates exists for a given week number
3. __Pause shows me if at least one of the pause values is FALSE()
4. if __Pause and __Active, then 1 - there's no need for else here
Now, this works perfectly - you can see my matrix visual here:
the 1 is to show me if the criteria is met during a given week, per person, service, and department. I've validated the data, and it's showing me exactly what I need to see.
But I really need to add up all those 1's, but I'm getting no totals at all - and if I try grouping things, I never get more than 1. Any idea of what I can do here?
Solved! Go to Solution.
Hi @grggmrtn ,
This is a normal problem when user calculate some expressions via measure. The reason is the total column has not the same filter context as the sub items but calculate by same DAX.
So please consider using HASONEVALUE() to let the measure know when use expression for sub items and when use expression for total.
Measure =
VAR _Active =
CALCULATE( COUNTROWS( DISTINCT( TABLE[Date] ) ) )
VAR _Pause =
CALCULATE( COUNTROWS( DISTINCT( TABLE[Pause] ) ), TABLE[Pause] = FALSE() )
VAR _summarize =
SUMMARIZE(
table,
[CPR],
[BorgerNavn],
[IndsatsNavn],
"Active", _Active,
"Pause", _Pause
)
VAR _add =
ADDCOLUMNS( _summarize, "if", IF( [Pause] = 1 && [Active] > 0, 1 ) )
VAR _sum =
SUMX( _add, [if] )
RETURN
IF(
HASONEVALUE( 'table'[Afdeling] ),
IF( __Pause = 1 && __Active > 0, 1 ),
_sum
)
Please modify this measure as appropriate to suit your model.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @grggmrtn ,
This is a normal problem when user calculate some expressions via measure. The reason is the total column has not the same filter context as the sub items but calculate by same DAX.
So please consider using HASONEVALUE() to let the measure know when use expression for sub items and when use expression for total.
Measure =
VAR _Active =
CALCULATE( COUNTROWS( DISTINCT( TABLE[Date] ) ) )
VAR _Pause =
CALCULATE( COUNTROWS( DISTINCT( TABLE[Pause] ) ), TABLE[Pause] = FALSE() )
VAR _summarize =
SUMMARIZE(
table,
[CPR],
[BorgerNavn],
[IndsatsNavn],
"Active", _Active,
"Pause", _Pause
)
VAR _add =
ADDCOLUMNS( _summarize, "if", IF( [Pause] = 1 && [Active] > 0, 1 ) )
VAR _sum =
SUMX( _add, [if] )
RETURN
IF(
HASONEVALUE( 'table'[Afdeling] ),
IF( __Pause = 1 && __Active > 0, 1 ),
_sum
)
Please modify this measure as appropriate to suit your model.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@grggmrtn This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
59 |
User | Count |
---|---|
197 | |
116 | |
107 | |
77 | |
70 |