topic Measure to filter on slicer selection in DAX Commands and Tips

Measure to filter on slicer selection

BIGDADE5000 — Mon, 02 Oct 2023 13:11:40 GMT

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.

Any help you can provide is greatly appreciated! Thank you!

Re: Measure to filter on slicer selection

Anonymous — Wed, 04 Oct 2023 03:36:18 GMT

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

Re: Measure to filter on slicer selection

BIGDADE5000 — Wed, 04 Oct 2023 12:38:52 GMT

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

Re: Measure to filter on slicer selection

BIGDADE5000 — Tue, 10 Oct 2023 20:06:37 GMT

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!