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

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

Reply
BIGDADE5000
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):

 

BIGDADE5000_0-1696018892541.png

 

 

 

 

 

 

 

 

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.
 
Any help you can provide is greatly appreciated!  Thank you!
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @BIGDADE5000 ,

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.

View solution in original post

3 REPLIES 3
BIGDADE5000
Regular Visitor

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

v-yiruan-msft
Community Support
Community Support

Hi @BIGDADE5000 ,

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.

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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.