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 everyone,
I'm new in Power BI and I want to do a new measure but the expressions I tried do not work. I want to count and then sum the results of different filters. Like in the following example of Qlik Sense I want to count the values in ActivityCode1 applying the two filters of the other two columns and the add it to the other expression.
count({<[ActivityType1]={'Field Repair','Help Desk','Solutions Activity'},
[ActivityCode1]={'Customer', 'Emergency', 'On Site Support','Repeat call'}>}[ActivityCode1]) +
count({<[ActivityStatus1]={'Cancelled'},
[SRType1]={'Service'},[SRProductName1]={'3R56','3R57','416898'}>}[SRProductName1])
Thanks in advance.
Solved! Go to Solution.
You were close enough. Try the following.
CALCULATE(COUNT(printingmensual[ActivityStatus1]) ;printingmensual[ActivityStatus1]="Done" ;printingmensual[ActivityCode1]="Call Open" )
The reason your attempt failed is that:
CALCULATE(COUNT(printingmensual[ActivityStatus1]) ;printingmensual[ActivityStatus1]="Done" )
is a shortcut for the following expression.
CALCULATE(COUNT(printingmensual[ActivityStatus1]) ;FILTER( ALL(printingmensual[ActivityStatus1]) ; [ActivityStatus1]="Done" ) )
However, there is no such shortcut for cases where 2 or more columns are used in a filter expression.
Hi @escalas,
As we are not familiar with Qlik Sense, it would be better if you could share the sample data table and expected results for us to think out of DAX.
Best Regards,
Qiuyun Yu
the first part as far as i can see would be
CALCULATE(COUNT([activitytype1],[activitytype1]="Field Repair"|| [activitytype1]="Help Desk"||[activitytype1]="Solutions Activity")
this is something like a countif function. Set the Counting in the first part and the filters by column in the second.
Unfortunately I didn't understand your second question. But maybe this will help you a little bit.
Thanks for you awnser @spuder,
That's more or less what I was looking for but I want to filter different columns and do the count in one of them. Something like this:
TotalVa = CALCULATE(COUNT(printingmensual[ActivityStatus1]);printingmensual[ActivityStatus1]="Done" && printingmensual[ActivityCode1]="Call Open" )
But I get the error 'only a single columns can be used in a true/false expression'. Any ideas how can I filter different columns in one COUNT?
You were close enough. Try the following.
CALCULATE(COUNT(printingmensual[ActivityStatus1]) ;printingmensual[ActivityStatus1]="Done" ;printingmensual[ActivityCode1]="Call Open" )
The reason your attempt failed is that:
CALCULATE(COUNT(printingmensual[ActivityStatus1]) ;printingmensual[ActivityStatus1]="Done" )
is a shortcut for the following expression.
CALCULATE(COUNT(printingmensual[ActivityStatus1]) ;FILTER( ALL(printingmensual[ActivityStatus1]) ; [ActivityStatus1]="Done" ) )
However, there is no such shortcut for cases where 2 or more columns are used in a filter expression.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |