cancel
Showing results for
Did you mean:
Helper III

## Help 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:

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.

1 ACCEPTED SOLUTION
Super User

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 10
Helper III

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".

Super User

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" }
)
)

Helper III

Neither one works. The first gives an error and the second gives me the 20K total again.

Super User

best if you send a sample file to us... mask any sensitive data..

also it would be great if you provide the correct output on a table

Helper III

There is a link to the data in the original post. It has the Access database with all of the tables and queries. No sensitive information is included. As for what I'd like to see it would look like this:

Date             Date            ............

3/14/2020   3/21/2020

Division ESD         157             157               ............

FMS        227             227             ............

Super User

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

Helper III

Exactly what you have. Thanks for the assistance.

Super User

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" }
)
)

Helper III

Sorry,

It doesn't give the correct value. It comes up as 20295 for each week and division.

Super User

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" }
)
)

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors