Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rbartlejr
Helper III
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:

 

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.

1 ACCEPTED SOLUTION

@rbartlejr 

 

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

 

View solution in original post

10 REPLIES 10
rbartlejr
Helper III
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".

@rbartlejr 

 

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

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

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

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

 

@rbartlejr 

 

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

 

Exactly what you have. Thanks for the assistance.

themistoklis
Community Champion
Community Champion

@rbartlejr 

 

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

Sorry,

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

@rbartlejr 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.