cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Measure to filter on slicer selection

I am trying to calculate a measure based on the selected value of a slicer.  I have two tables 'STD Hours - SAP' and 'Worked Hours' with a relationship on line detail between the two.  I want the measure to take the value of the selected 'Line Detail' and calculate a measure on the filtered value.  For instance, if user selects "WD Cell 4 (TO021119/120)", then sum all of the hours earned for that line detail (from 'STD Hours - SAP') and divide out the worked hours (from 'Worked Hours') for that line detail (see below):

I can get the measure to work with the following DAX:

DLE = IFERROR(SUM('STD Hours - SAP'[HMOD Earned])/CALCULATE(SUM('Worked Hours'[Hours]),'Worked Hours'[Line Detail] = "WD Cell 4 (TO021119/120)"), "N/A")
but not dynamically based on the slicer selection.  I have searched and found similar posts but can't figure out how to write it, not sure what I am missing.

1 ACCEPTED SOLUTION
Community Support

You can update the formula of measure [DLE] as below:

``````DLE =
VAR SelectedLineDetail = SELECTEDVALUE('Worked Hours'[Line Detail])
RETURN
IFERROR(
SUM('STD Hours - SAP'[HMOD Earned]) /
CALCULATE(SUM('Worked Hours'[Hours]), 'Worked Hours'[Line Detail] = SelectedLineDetail),
"N/A"
)``````

Remember, this method assumes that your slicer is single-select. If multiple selections are allowed in your slicer, you might need to adjust your approach accordingly as below:

``````DLE =
VAR SelectedLineDetails =
ALLSELECTED ( 'Worked Hours'[Line Detail] )
RETURN
IFERROR (
SUM ( 'STD Hours - SAP'[HMOD Earned] )
/ CALCULATE (
SUM ( 'Worked Hours'[Hours] ),
'Worked Hours'[Line Detail] IN SelectedLineDetails
),
"N/A"
)``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Regular Visitor

Thank you so much, Rena!  It made total sense once I saw your recommendation.

Community Support

You can update the formula of measure [DLE] as below:

``````DLE =
VAR SelectedLineDetail = SELECTEDVALUE('Worked Hours'[Line Detail])
RETURN
IFERROR(
SUM('STD Hours - SAP'[HMOD Earned]) /
CALCULATE(SUM('Worked Hours'[Hours]), 'Worked Hours'[Line Detail] = SelectedLineDetail),
"N/A"
)``````

Remember, this method assumes that your slicer is single-select. If multiple selections are allowed in your slicer, you might need to adjust your approach accordingly as below:

``````DLE =
VAR SelectedLineDetails =
ALLSELECTED ( 'Worked Hours'[Line Detail] )
RETURN
IFERROR (
SUM ( 'STD Hours - SAP'[HMOD Earned] )
/ CALCULATE (
SUM ( 'Worked Hours'[Hours] ),
'Worked Hours'[Line Detail] IN SelectedLineDetails
),
"N/A"
)``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

How would you propose to show all the values in a multi-row card?  The DAX, you provided, works great but I am trying to expand it now.  I want to show the 'DLE' for all lines on a multi-row card.
Should I open another topic?  Thank you for your help!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors