Search code examples
powerbidaxselectedvalue

Using SELECTEDVALUE in DAX not working correctly

I have written a DAX code which worked perfectly fine, however, I wanted to add another clause within which says that if the row is for a branch type that equals to IN STORE, it should also do the same SUMX over the summarized table, but as you can see from the screenshot of the current output its returning the incorrect result.

totalAverageBaysFinalVersion =
VAR onlyCampaignFiltered =
    ISFILTERED ( 'Campaign Overview'[campaign] )
        && NOT CALCULATE (
            ISCROSSFILTERED ( 'range_plans' ),
            REMOVEFILTERS ( 'Campaign Overview'[campaign] )
        )
            || ISFILTERED ( 'Calendar'[weekNumWeekCommence] )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Campaign Overview'[branch_type] ) = "IN STORE", --Additional Code
            SUMX (
                SUMMARIZE (
                    range_plans,
                    range_plans[branch],
                    range_plans[range_name],
                    range_plans[number_bays]
                ),
                range_plans[number_bays]
            ),
        IF (
            onlyCampaignFiltered,
            SUMX (
                SUMMARIZE (
                    range_plans,
                    range_plans[branch],
                    range_plans[range_name],
                    range_plans[number_bays]
                ),
                range_plans[number_bays]
            ),
            [Total Avg Bays Campaign Overview]
        )
    )

Output:

enter image description here

Expected Output:

Campaign                               Branch Type             Avg.Bays
2023/07/19 - 2023/08/15 Summer 2         IN STORE              7,806.43
2023/07/19 - 2023/08/15 Summer 2          ONLINE                  0

How would I go about checking whether the row is IN STORE and making sure it returns the correct value?

Solution

  • Following on from the comments and chat - the row context is affecting the results you are expecting. See if this works where you REMOVEFILTERS for one of the columns in the table for when "IN STORE"...

    totalAverageBaysFinalVersion =
  VAR onlyCampaignFiltered =
    ISFILTERED ( 'Campaign Overview'[campaign] )
    && NOT CALCULATE ( ISCROSSFILTERED ( 'range_plans' ), REMOVEFILTERS ( 'Campaign Overview'[campaign] )  )
    || ISFILTERED ( 'Calendar'[weekNumWeekCommence] )

  RETURN SWITCH( TRUE(),
    SELECTEDVALUE ( 'Campaign Overview'[branch_type] ) = "IN STORE", [totalRangePlanBays]( REMOVEFILTERS ( 'Campaign Overview'[branch] ) ),
    onlyCampaignFiltered, [totalRangePlanBays],
    [Total Avg Bays Campaign Overview]
  )