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

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.

Reply
bvy
Helper V
Helper V

Help clean up this simple DAX calculation

Hi. Given a table of values, something like below, I want a measure that will return the "within range" percentage for each category. So if 3 out of 4 values are with in range (as shown below) the measure should return 0.75 for category A. 

 

Table1

Category.....RangeHigh.....RangeLow.....Value
A305052
A305045
A305040
A305038

[extra dots for formatting]

 

What I've come up with is below, but the problems are:

  • The category is hard coded -- I'd like it driven off of a slicer or filter, and 
  • It's messy -- I think there's a clearer more compact way to write the DAX. 

 

 

PctInRange = 1 - CALCULATE(COUNTROWS(Table1), FILTER(Table1, (Table1[Value] < Table1[RangeLow] || Table1[Value] > Table1[RangeHigh]) && Table1[Category] = "A")) / CALCULATE(COUNTROWS(Table1), Table1[Category] = "A")

 

 

 

Can someone help? Thanks for taking a look! 

1 ACCEPTED SOLUTION
bcdobbs
Super User
Super User

A few thoughts and a question.

  • In your initial COUNTROWS you'd be better just to directly COUNTROWS of the table returned from the filter.
  • You can make your code a lot more readable by using variables and formatting it.

Those combined would give:

PctInRange = 
VAR tblGroupAInRange = 
    FILTER(
        Table1, 
        (Table1[Value] < Table1[RangeLow] || Table1[Value] > Table1[RangeHigh]) 
        && Table1[Category] = "A")

VAR cntGroupAInRange = COUNTROWS ( tblGroupAInRange )
VAR cntGroupA = 
    CALCULATE(
        COUNTROWS ( Table1 ), 
        Table1[Category] = "A"
    )

VAR Result = 1 - DIVIDE ( cntGroupAInRange, cntGroupA)
RETURN Result

 

In terms of making it more dynamic depending on how you're intending to use the measure you could just get rid of the hard coded category = A filters. Sliceing on category would pass the filter straight into both.

 

Lastly are the high/low values the same for all of category A? If so moving them out to a category dimension might help further.

 

Share a little on how you'd like to use the measure and we can take it further.

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

3 REPLIES 3
bcdobbs
Super User
Super User

A few thoughts and a question.

  • In your initial COUNTROWS you'd be better just to directly COUNTROWS of the table returned from the filter.
  • You can make your code a lot more readable by using variables and formatting it.

Those combined would give:

PctInRange = 
VAR tblGroupAInRange = 
    FILTER(
        Table1, 
        (Table1[Value] < Table1[RangeLow] || Table1[Value] > Table1[RangeHigh]) 
        && Table1[Category] = "A")

VAR cntGroupAInRange = COUNTROWS ( tblGroupAInRange )
VAR cntGroupA = 
    CALCULATE(
        COUNTROWS ( Table1 ), 
        Table1[Category] = "A"
    )

VAR Result = 1 - DIVIDE ( cntGroupAInRange, cntGroupA)
RETURN Result

 

In terms of making it more dynamic depending on how you're intending to use the measure you could just get rid of the hard coded category = A filters. Sliceing on category would pass the filter straight into both.

 

Lastly are the high/low values the same for all of category A? If so moving them out to a category dimension might help further.

 

Share a little on how you'd like to use the measure and we can take it further.

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
jgeddes
Super User
Super User

I am thinking you could accomplish this using a calculated column in table 1 that tests whether a Value is in range and returns something like "isInRange" or notInRange". Then write a measure that divides the count of "isInRange" by all values in the calculated column to get the percentage. That should allow filtering and slicing by categories that are in Table 1.




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

Proud to be a Super User!





I could do that but I don't want to add excessive overhead to the table if it could all be managed in DAX without impacing performance, which I think it can. 

 

Can someone suggest how to modify the DAX, please? 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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