Forum Discussion

msmays5's avatar
msmays5
Icon for Helper II rankHelper II
2 years ago

MEDIANX Returning Incorrect Value with ALLSELECTED

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.

5 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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:

    1. 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.

    2. 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.

     
     
     
    • msmays5's avatar
      msmays5
      Icon for Helper II rankHelper II
      2 years ago

      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

      • 123abc's avatar
        123abc
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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