Forum Discussion
Helper IIIHelp with Dax formula for Matrix
I'm having an issue with a DAX formula. I've tried using the FILTER function but I just can't get it correct (I'm still very novice with DAX). I have a database in Acess that comes from an Excel report. It can be found here:
https://www.dropbox.com/s/dvn5k7d8bh3d43q/Department_KPIs.accdb?dl=0
What I need to do is to get the Value from the KPI data into a matrix filtered by the KPI measures Division code by date and Metric_Code. For example:
Date Date ............
3/14/2020 3/21/2020
Division ESD 157 157 ............
FMS 227 227 ............
These would be for the "ActCnt" in the Metic_Code column.
Here is a measure I made that works for a stacked bar chart but not the matrix since it's summed:
ActCnt =
CALCULATE(
SUM('qry_Employees'[SumOfValue]),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
I've tried using the FILTER function but just can't seem to get it right. Any help would be greatly appreciated.
Could you please have a look at the following link and let me know if this is what you would like to see?
https://www.dropbox.com/s/lovggqivwownchj/PowerBI_data.pbix?dl=0
10 Replies
- themistoklis
Community Champion
Can you try the following formula:
ActCnt =
CALCULATE (
SUM('qry_Employees'[SumOfValue]),
FILTER (
ALLEXCEPT('qry_Employees', 'qry_Employees'[Date]),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
)
rbartlejrSorry,
It doesn't give the correct value. It comes up as 20295 for each week and division.
- themistoklis
I think i used the wrong function. Try the following
ActCnt =
CALCULATE (
SUM('qry_Employees'[SumOfValue]),
FILTER (
ALLSELECTED('qry_Employees'[Date], 'qry_Employees'[Division]),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
)
- rbartlejr
It comes up with an error for 'qry_Employees'[Division]. It says "Multiple arguments are not allowed in the ALLSELECTED function when the first argument is a table reference".
- themistoklis
Try either this:
ActCnt =
CALCULATE (
SUM('qry_Employees'[SumOfValue]),
FILTER (
ALLSELECTED('qry_Employees'[Date], 'qry_Employees'[Division]),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
)Or this
ActCnt =
CALCULATE (
SUM('qry_Employees'[SumOfValue]),
FILTER (
ALLSELECTED('qry_Employees'),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
)- rbartlejr
Neither one works. The first gives an error and the second gives me the 20K total again.