DAX Combining IF with SELECTEDVALUE Showing Incorrect Result

[ahiemstra]

I’m hoping someone can help me. I’m looking to combine an IF statement with SELECTEDVALUE. I found a DAX on the forum that I was hopeful would work for me however it’s giving me incorrect results.

My column chart contains Provider on the x-axis and I’m looking to visualize average hours per day for various types of time (driven by a slicer) on the y-axis. The types of time include:

Indirect Care Time

No Show Cancel Time

PFHT Activity Time

Program Develop Time

Rx Renewal Time

Secure Message Time

I’ve created an Avg/Day measure for each type of time listed above. As an example, the measure for Indirect Care Time looks like this:

Avg Indirect Care Time/Day = SUMX(FILTER('ADMIN','ADMIN'[Activity Type]="Indirect Care Time"),'ADMIN'[Time])/

COUNTX (

    SUMMARIZE ( 'ADMIN', 'ADMIN'[Employee ID], 'ADMIN'[Date]  ),

    'ADMIN'[Date]

)

The reason I needed to create the measure above is because when I simply tried adding the Time column to the y-axis and showing as an Average, I was getting an incorrect result. For example, for Kyla it resulted in an average of 1.24. See ‘Sample Report 1' in the PBIX file.

For Kyla, the average should be 1.22 (sum of indirect care time = 52.25 hours / 43 days = 1.22).

If I don’t use the Activity Type slicer and plot the Avg Indirect Care Time/Day measure (shown above) on the y-axis, you’ll see the result is correct, 1.22 hours/day. See ‘Sample Report 2' in the PBIX file.

Moving along to my actual issue … the DAX I found to combine the IF and SELECTEDVALUE functionality which I thought would work is as follows:

Avg Time/Day =

SWITCH(

    SELECTEDVALUE('ADMIN'[Activity Type]),

    "Indirect Care Time", [Avg Indirect Care Time/Day],

    "No Show Cancel Time", [Avg No Show Cancel Time/Day],

    "PFHT Activity Time", [Avg PFHT Activity Time/Day],

    "Program Develop Time", [Avg Program Develop Time/Day],

    "Rx Renewal Time", [Avg Rx Renewal Time/Day],

    "Secure Message Time", [Avg Secure Message Time/Day],

    )

See 'Sample Report 3' in the PBIX file. The result from the IF + SELECTEDVALUE DAX above displays an average/day consistent with if I simply plotted the Time column on the y-axis (same result as Sample Report 1.) For Kyla, it’s showing 1.24 rather than 1.22.

I’m not understanding why this is happening. I’m using the same measure within the DAX as I did in Sample Report 2 and the reult in Sample Report 2 is showing correctly.

Does anyone know why this is happening and how I can fix it?

I have shared the PBIX file here: https://ln5.sync.com/dl/e4b294030/fa8r4fk4-5bfy5k7g-cqpw2uv8-xgjeyb3k

[selimovd]

Hello @ahiemstra ,

I took a look at your case. The problem is not the selected value, the problem is that you add another filter to the visual. To make it easier to undestand, we will only focus on Kyla for the moment.

In the sheet "Sample Report 2", the only filter you have is on "Fiscal Year" = 2023/24:

On "Sample Report 3", you add another filter to the game, by filtering to "Activty Type" = "Indirect Care Time":

This has an impact on the divisor of your DAX formula, for the [Avg Indirect Care Time/Day] this is:

COUNTX (
    SUMMARIZE ( 'ADMIN', 'ADMIN'[Employee ID], 'ADMIN'[Date]  ),
    'ADMIN'[Date]
)

You summarize by [Employee ID] and [Date]. Now in the Report 3, you have an additional filter that is filtering for "Indirect Care Time". In you case this is filtering out this row:

This row will be considered in Report 2 as you don't set an explicit filter on "Indirect Care Time", but you set it on Report 3.

For that reason you get in:

Report 2: 52.25 / 43 rows = 1.22

Report 3: 52.25 / 42 rows = 1.24

This means in my opinion you have to filter both elements, the numerator and the denominator by the same Activity Type.

Also the SUMX and COUNTX approach is not recommended as they are iterative functions, that could become quite expensive with a lot of data. It's a better approach to change the filter context with CALCULATE.

My proposal would be the following measure:

Avg Indirect Care Time/Day NEW = 
-- Sum the Time and filter to "Indirect Care Time"
VAR _SumTime = CALCULATE( SUM( ADMIN[Time]), 'ADMIN'[Activity Type]="Indirect Care Time")
-- Create the virtual table, but filter already to "Indirect Care Time"
VAR _SummarizeEmployeeDate =
SUMMARIZE (
    FILTER (ADMIN, ADMIN[Activity Type] = "Indirect Care Time"),
    'ADMIN'[Employee ID],
    'ADMIN'[Date]
)
-- Count the rows of the filtered table
VAR _AmountDays = COUNTROWS( _SummarizeEmployeeDate )
-- divide both results
VAR _Result = DIVIDE( _SumTime, _AmountDays )
RETURN
-- return result
_Result

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[ahiemstra]

Hello @selimovd,

The Fiscal Year slicer isn’t relevant here as all data in my current dataset is from fiscal 2023/24 (I should have removed that slicer so as not to confuse the situation.)

I can see what you’re saying. In my ‘Avg Indirect Care Time/Day’ measure the first part is computing 52.25 and the second part 43. When I add the Activity Type slicer, the first part of the measure is still 52.25 but the second part is 42 days. Hence the reason the result is changing from 1.22 to 1.24.

Really, I was just trying to be efficient, and rather than having a separate report for the 6 different types of time all using their respective ‘Avg ________ Time/Day’ measures (which calculate correctly), I thought I would create 1 report structure and let the viewer select the type of time they wish to visualize and the chart would automatically populate based on the Activity Type selection. It seems however the slicer is causing the issue.

I tried your proposed suggestion (or at least I think I did) however I’m still getting 1.24 rather than 1.22.

I created the ‘Avg Indirect Care Time/Day NEW’ measure you suggested. I then created a new measure using the SELECTEDVALUE piece and pulling in your new measure for Indirect Care Time:

Avg Time/Day NEW =

SWITCH(

    SELECTEDVALUE('ADMIN'[Activity Type]),

    "Indirect Care Time", [Avg Indirect Care Time/Day NEW],

    "No Show Cancel Time", [Avg No Show Cancel Time/Day],

    "PFHT Activity Time", [Avg PFHT Activity Time/Day],

    "Program Develop Time", [Avg Program Develop Time/Day],

    "Rx Renewal Time", [Avg Rx Renewal Time/Day],

    "Secure Message Time", [Avg Secure Message Time/Day]

    )

As mentioned, this is resulting in 1.24 for Kyla rather than 1.22. Perhaps I misunderstood your solution?

[selimovd]

Hey @ahiemstra ,

if the measure isn't working, you can paste it here, I can take a look.

For the Switch, you could also use a Field Parameter. In my opinion that's a little more elegant.

The fiscal year doesn't have any impact on the wrong calculation, as it's just another slicer.

The issue is not that you tried to be efficient and it didn't work. The issue is that the calculation is just wrong like that.

You set the filter in the first part of the division but then divide by all rows in the second. So you have to set the filter explicitly in both parts

Or as an alternatv approach you can create a generic measure and put the "Activity Type" in an own dimension. When you filter then the dimensional table, all parts of the division are also filtered.

Please take a look at the attached file, I created that as an example.

This would then be the generic measure:

Avg Measure generic = 
VAR _SumTime = SUM( ADMIN[Time])
VAR _SummarizeEmployeeDate =
SUMMARIZE (
    ADMIN,
    'ADMIN'[Employee ID],
    'ADMIN'[Date]
)
VAR _AmountDays = COUNTROWS( _SummarizeEmployeeDate )
VAR _Result = DIVIDE( _SumTime, _AmountDays )
RETURN
_Result

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[_AAndrade]

Hi,

@ahiemstra I've been analysing your pbix and I'm afraid I have to say that the Sample Report 2 is wrong.
If you export your data to an excel sheet and make a quick pivot table you will see this summary:

As you can see, for Kyla and Activity Type = "Indirect Care Time" there are 52.25 hours and 42 days (instead of 43 days), so the average is 1.24.

Your "Avg Indirect Care Time/Day" measure is wrong because of this part:

This part gives you 43 days (that's right) but it doesn't take into account any type of activity, it's 43 days for all types of activity and it's not you want to, right? You only want the count of days for Activity Type = "Indirect Care Time".
You could replace your measure to this one:

I hope I could help you to understand why you are getting different values.

[ahiemstra]

Hello @_AAndrade,

No, I’m looking for the divisor to be 43 days not 42 days. I want the divisor to be reflective of the total days worked, not just the total days where the provider recorded Indirect Care Time. I get what you are saying, however that’s not the issue here.

[_AAndrade · Accepted solution]

@ahiemstra If the 43 days is the right number and you want to have one measure to allow the user to choose the activity type, my solution is this:

In the Sample Report 3 if you put this New Avg Measure instead of your Avg Time/Day you will have this result:

Kyla = 1.22

And if you choose other Activity Type on the slicer you will have the respective chart.
I hope this is what are you looking for, if you need any help please let me know.

[ahiemstra]

Thanks @_AAndrade this did the trick!