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
DHB
Helper V
Helper V

Use Filters in Measure DAX

I have a list a set of data which I've used this DAX to calculate percentages of the total records;

 

DIVIDE(COUNT(ACTIVITY[PERSON_CODE]),CALCULATE(COUNT(ACTIVITY[PERSON_CODE]),ALL(ACTIVITY)))

 

In one of my report pages the data is filtered to one particular year and 'active' users only so I'd like to create a new measure which takes this into account so that all 'active' users in 2019=100%.  At the moment it's giving me 60% because it's taking into account 2020 and other user states.  This is my first attempt at the new DAX but it doesn't work;

 

DIVIDE(FILTER(ACTIVITY,[YEAR]=2019&(ACTIVITY[ENROL_WF]="active"))[COUNT(ACTIVITY[PERSON_CODE]),CALCULATE(COUNT(ACTIVITY[PERSON_CODE]),ALL(ACTIVITY)))
 
Can anyone give me any advice on how to fix it up?
 
Thanks,
 
DHB
2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

I would first try your original measure with ALLSELECTED() instead of ALL().

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

AiolosZhao
Memorable Member
Memorable Member

Hi @DHB ,

 

You may try to use the below measure:

DIVIDE(COUNT(ACTIVITY[PERSON_CODE]),CALCULATE(COUNT(ACTIVITY[PERSON_CODE]),ALLSELECTED(ACTIVITY)))

 Please try.

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
AiolosZhao
Memorable Member
Memorable Member

Hi @DHB ,

 

You may try to use the below measure:

DIVIDE(COUNT(ACTIVITY[PERSON_CODE]),CALCULATE(COUNT(ACTIVITY[PERSON_CODE]),ALLSELECTED(ACTIVITY)))

 Please try.

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




mahoneypat
Employee
Employee

I would first try your original measure with ALLSELECTED() instead of ALL().

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thanks Pat, that works but I have another issue now.  I'm representing that measure in a gauge visual and when I apply filters in the report the value stays at 100%.  With the DAX I used before it only showed 60% but when I used filters this would go up and down between 0-60.  Can I use the ALLSELECTED version and still have the gauge correspond to the filters I use somehow?

@DHB , try like

DIVIDE(calculate([COUNT(ACTIVITY[PERSON_CODE]),FILTER(ACTIVITY,ACTIVITY[YEAR]=2019&(ACTIVITY[ENROL_WF]="active"))),CALCULATE(COUNT(ACTIVITY[PERSON_CODE]),ALL(ACTIVITY)))

Hi @DHB ,

 

You need to tell us what's your expected result finally. Because the allselected function is to calculate % by person code.

 

Please show us the sample data(what you want) and show us what you want(screenshot or image).

It's better to give some examples for different scenarios.

 

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This page in my report is filtered to 2019 and active users (1102 in total).  My original DAX didn't filter out other years or user states so gave me a % of 60.9 in the gauge.  the new DAX gives me the right % but it doesn't change when I move the slider on the left.

 

Sorry - picture wouldn't upload

Capture.PNG

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.