Measure incorporating slicer value

[ahiemstra]

I'm attaching a PBIX file and hoping someone might be able to assist.

I've created two separate measures: Avg Encounters/Day Discipline and Avg Encounters/Day Provider. The calculation for both measures is identical, but for the "Discipline" measure, I want the result to be based on the selection in the 'Activity Description' slicer and for the "Provider" measure, I want the result to be based on the selection in the 'Provider' slicer.

I’ve been researching how to make this work but haven’t had much success. I came across the SELECTEDVALUE function, which I tried implementing, but it’s leading to errors, as you’ll see in the file.

Ultimately, I’m aiming to create a chart (either line or column) that displays both the average for the Provider alongside the average for their Discipline over time.

For example, for Kyla B who is an RD (RD 1 ON 1) the chart would look like this:

https://ln5.sync.com/dl/6eafbd230#mt2abezm-px98hehc-d5a4pvsi-dtkjvcu3

[v-yajiewan-msft]

Hi @ahiemstra hello Greg_Deckler, thank you for your prompt reply!

 Try the following measures to meet your requirements:

Avg Encounters/Day Discipline2 = 
VAR TotalEncounters = CALCULATE(
    COUNT('1 ON 1'[Encounter Time]),
    '1 ON 1'[Activity Description] = SELECTEDVALUE('1 ON 1'[Activity Description]),REMOVEFILTERS('1 ON 1'[Employee Full Name]))

VAR TotalDays = CALCULATE(
    DISTINCTCOUNT('1 ON 1'[Encounter Date]),
    '1 ON 1'[Activity Description] = SELECTEDVALUE('1 ON 1'[Activity Description]),
    REMOVEFILTERS('1 ON 1'[Employee Full Name])  
)

RETURN

DIVIDE(TotalEncounters, TotalDays, 0)

Avg Encounters/Day Provider2 = 
VAR TotalEncounters = CALCULATE(
    COUNT('1 ON 1'[Encounter Time]),
    '1 ON 1'[Employee Full Name] = SELECTEDVALUE('1 ON 1'[Employee Full Name]),
    REMOVEFILTERS('1 ON 1'[Activity Description])  
)

VAR TotalDays = CALCULATE(
    DISTINCTCOUNT('1 ON 1'[Encounter Date]),
    '1 ON 1'[Employee Full Name] = SELECTEDVALUE('1 ON 1'[Employee Full Name]),
    REMOVEFILTERS('1 ON 1'[Activity Description])  
)

RETURN

DIVIDE(TotalEncounters, TotalDays, 0)

 Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ahiemstra · Accepted solution]

I spent some more time working on the DAX and was able to adjust the part in the denominator that wasn't calculating correctly, and now it's working as expected.

[ahiemstra]

Hi @v-yajiewan-msft,

Thanks for your response. I think we're making progress.

It seems there may be an issue with the TotalDays component. While it's calculating correctly for the Avg Encounters/Day Provider2 measure, it's giving incorrect results for the Avg Encounters/Day Discipline2 measure.

The values for the Avg Encounters/Day Discipline2 measure are currently too high. For instance, in April, the discipline average should be around 4.0, but in your chart, it’s showing just under 20.

The result shuold look more like this:

I've successfully used standalone versions of the discipline and provider measure in other PBIX files, and the DAX that works properly in those cases looks like this:

Avg Encounters/Day = COUNT('1 ON 1'[Encounter Time]) /
COUNTX(
SUMMARIZE('1 ON 1', '1 ON 1'[Employee ID], '1 ON 1'[Encounter Date]),
'1 ON 1'[Encounter Date]
)

Would it be possible to update your measures to use this DAX formula while also incorporating the SELECTEDVALUE and REMOVEFILTERS components you've used?

[Greg_Deckler]

@ahiemstra You'll likely need to have slicers that are disconnected from your visual/data table. Then you can simply use MAX or SELECTEDVALUE to grab the desired value and then you can incorporate that into your measure.

[ahiemstra]

@Greg_Deckler can you help me understand why I need disconnected slicers? I'm not familiar with these, I've only ever created slicers that are directly tied to my data table. I'm not sure I would know how to go about creating disconnected slicers and then incorporating them into the measures.