topic MEDIANX Returning Incorrect Value with ALLSELECTED in DAX Commands and Tips

MEDIANX Returning Incorrect Value with ALLSELECTED

msmays5 — Wed, 29 Nov 2023 01:02:17 GMT

Hi all,

I've included all sample data and key DAX formulas here. My question is as follows:

I have a KPI [% Release Completed Points] which is [# of Release Completed Points] / [# of Release Loaded Points]. I am trying to calculate the MEDIAN for whichever Releases and Teams a user selects. No matter what I seem to try, I am not getting correct values. My latest attempts is as follows, but it is not working:

VAR _SelectedReleased = ALLSELECTED ( Releases[Release] ) VAR _SelectedTeams = FILTER ( ALLSELECTED ( 'Delivery Team'[Delivery Team] ), 'Delivery Team'[Delivery Team] <> "Team 7" ) VAR _AllRows = CROSSJOIN ( _SelectedReleased, _SelectedTeams ) VAR _Result = MEDIANX ( _AllRows, CALCULATE ( [% Release Completed Points], _SelectedReleased, _SelectedTeams ) ) RETURN _Result

Any guidance on where I am going wrong would be greatly appreciated.

Thanks in advance for any help you can provide.

Re: MEDIANX Returning Incorrect Value with ALLSELECTED

123abc — Wed, 29 Nov 2023 05:31:02 GMT

Here's a modified version of your DAX code:

VAR _SelectedReleased = ALLSELECTED ( Releases[Release] )
VAR _SelectedTeams =
VALUES (
'Delivery Team'[Delivery Team]
)
VAR _AllRows =
CROSSJOIN (
_SelectedReleased,
_SelectedTeams
)
VAR _Result =
MEDIANX (
_AllRows,
CALCULATE (
[% Release Completed Points],
_SelectedReleased,
_SelectedTeams
)
)
RETURN
_Result

Changes made:

Use VALUES() instead of FILTER(): Instead of using FILTER to exclude "Team 7", I've used VALUES to get all the selected teams. This will automatically exclude "Team 7" and give you a distinct list of selected teams.
Changed _SelectedTeams in the CALCULATE function: I changed _SelectedTeams to use VALUES() in the CALCULATE function. This ensures that the context transition works correctly when calculating [% Release Completed Points] for each row in _AllRows.

Give this a try and see if it produces the desired result. If you're still facing issues, please provide more details about the data model and the structure of your tables, and I'll do my best to assist you further.

Re: MEDIANX Returning Incorrect Value with ALLSELECTED

msmays5 — Wed, 29 Nov 2023 17:30:27 GMT

Thank you for your willingness to help! However, there are two issues I'm encountering:

1. My measure was misnamed -- it should be the Median across both the selected teams and the selected releases - so I believe I should be using ALLSELECTED(Deliver Team[Delivery Team])

2. When I implemented your code, I was still not getting the correct median, even when just looking across releases. I'm getting the same value for both the base measure and the median measure. Please see here for a visual of what I'm seeing. Could this have something to do with the base measure having a KEEPFILTERS in it?

Thanks again for your assistance

Re: MEDIANX Returning Incorrect Value with ALLSELECTED

123abc — Thu, 30 Nov 2023 07:05:20 GMT

If you want to calculate the median across both selected teams and selected releases, you can use ALLSELECTED on both dimensions. Also, the issue you're facing might be related to the KEEPFILTERS in the base measure.

Let's modify the formula to address these concerns:

VAR _Result =
MEDIANX(
ALLSELECTED(Releases[Release], 'Delivery Team'[Delivery Team]),
[% Release Completed Points]
)

RETURN
_Result

This modification uses ALLSELECTED on both the Releases and Delivery Team dimensions within the MEDIANX function.

Regarding the issue with the base measure containing KEEPFILTERS, it depends on the context and the logic within the base measure. If the base measure uses KEEPFILTERS in a way that interferes with the calculation of the median, you might need to modify the base measure as well.

If the base measure is using KEEPFILTERS to enforce a specific context, you may need to adjust your base measure logic to work well with the overall calculation. Alternatively, you can provide more details about your base measure, and I can assist you in modifying it if necessary.

Please make sure to replace [% Release Completed Points] with the actual expression you are using for calculating the release completed points.

If you still encounter issues, please provide more details about the base measure, and I'll do my best to assist you further.

Re: MEDIANX Returning Incorrect Value with ALLSELECTED

Anonymous — Tue, 05 Dec 2023 03:09:20 GMT

Hi msmays5 ,

Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

Re: MEDIANX Returning Incorrect Value with ALLSELECTED

msmays5 — Tue, 12 Dec 2023 16:35:01 GMT

Apologies for the delay in getting back to you, I've been out of pocket. Thank you for following up.

In your example, I recieve the error: "All column arguments of the ALL/ALLNOBLANKROW/ALLSELECTED/REMOVEFILTERS function must be from the same table."

Additionally, I've moved my base measure to the median calculation, hoping that would resolve the issue, but I'm still getting incorrect results:

VAR _SelectedReleased = ALLSELECTED ( Releases[Release] ) VAR _SelectedTeams = ALLSELECTED ( 'Delivery Team'[Delivery Team] ) VAR _AllRows = CROSSJOIN( _SelectedReleased, _SelectedTeams ) VAR _Result = MEDIANX ( _AllRows, VAR _Nume = CALCULATE ( [# of Release Completed Points], REMOVEFILTERS ( Releases ), _SelectedReleased, REMOVEFILTERS ( 'Delivery Team' ), _SelectedTeams ) VAR _Denom = CALCULATE( [# of Release Loaded Points], REMOVEFILTERS ( Releases ), _SelectedReleased, REMOVEFILTERS ( 'Delivery Team' ), _SelectedTeams ) VAR _Result2 = DIVIDE ( _Nume, _Denom ) RETURN _Result2 ) RETURN _Result