SWITCH measures - sorting issue

[AndySmith]

I have a few switch measures in order to toggle between various time frame calcs for customer sales (MTD, YTD and MAT). They seem to calculate correctly, but if I try to sort by the Sales value measure, the Discount % calculations dissapear. Any ideas why this might be or how to get around it? User will want/need to sort by both columns . Thanks.

 

Switch - Customer TimeFrame - Wsale =

VAR SelectedFrame = SELECTEDVALUE('Slicer - TimeFrame'[Select Time Frame], "MTD")

VAR WsaleValue =

    SWITCH(

        TRUE(),

        SelectedFrame = "MTD", CALCULATE([VE - Wholesale Amt MTD]),

        SelectedFrame = "YTD", CALCULATE([VE - Wholesale Amt FYTD]),

        SelectedFrame = "MAT", CALCULATE([VE - Wholesale Amt MAT]),

        CALCULATE([VE - Wholesale Amt])  

    )

RETURN

    WsaleValue  

Switch - Customer TimeFrame - Discount % =

VAR SelectedFrame = SELECTEDVALUE('Slicer - TimeFrame'[Select Time Frame], "MTD")

VAR DiscountResult =

    SWITCH(

        TRUE(),

        SelectedFrame = "MTD", [VE - Discount % MTD],

        SelectedFrame = "YTD", [VE - Discount % FYTD],

        SelectedFrame = "MAT", [VE - Discount % MAT],

        SelectedFrame = "Date Range", [VE - Discount %],

        0  

    )

RETURN DiscountResult

[AndySmith]

Thanks again! Sadly still no luck. There are definitley times when there are no discounts but that shouldnt matter for the measure. Everything calculates perfectly well then the disc % just disappears when i try to sort  

[Anonymous · Accepted solution]

Hi @AndySmith,

I think your issue is based on your data model. Could you share a sample file with us and hsow us a screenshot with the result you want. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

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

[AndySmith]

Thanks Rico

I think it may be the model. Is tabular model but there are blank rows when i bring in every dimension such as customer or product etc. Will consult with the IT team. 

[AndySmith]

Thanks for the reply. I get an error message saying that COALESCE is not a function and cannot be found? I am using a tabular model. Is that likely to make a difference?

[rajendraongole1]

Hi @AndySmith -The COALESCE function was introduced in more recent versions of Power BI and SSAS (post-SQL Server 2016).

COALESCE not being recognized in your tabular model is likely because COALESCE is not supported in DAX within earlier versions of SQL Server Analysis Services (SSAS) Tabular Models. 

you can try with iferror

Switch - Customer TimeFrame - Discount % =
VAR SelectedFrame = SELECTEDVALUE('Slicer - TimeFrame'[Select Time Frame], "MTD")
VAR DiscountResult =
SWITCH(
TRUE(),
SelectedFrame = "MTD", IFERROR([VE - Discount % MTD], 0),
SelectedFrame = "YTD", IFERROR([VE - Discount % FYTD], 0),
SelectedFrame = "MAT", IFERROR([VE - Discount % MAT], 0),
SelectedFrame = "Date Range", IFERROR([VE - Discount %], 0),
0
)
RETURN DiscountResult

or if isblank() function too

Switch - Customer TimeFrame - Discount % =
VAR SelectedFrame = SELECTEDVALUE('Slicer - TimeFrame'[Select Time Frame], "MTD")
VAR DiscountResult =
SWITCH(
TRUE(),
SelectedFrame = "MTD", IF(ISBLANK([VE - Discount % MTD]), 0, [VE - Discount % MTD]),
SelectedFrame = "YTD", IF(ISBLANK([VE - Discount % FYTD]), 0, [VE - Discount % FYTD]),
SelectedFrame = "MAT", IF(ISBLANK([VE - Discount % MAT]), 0, [VE - Discount % MAT]),
SelectedFrame = "Date Range", IF(ISBLANK([VE - Discount %]), 0, [VE - Discount %]),
0
)
RETURN DiscountResult

[rajendraongole1]

Hi @AndySmith - please find the modified measure for %

Switch - Customer TimeFrame - Discount % =
VAR SelectedFrame = SELECTEDVALUE('Slicer - TimeFrame'[Select Time Frame], "MTD")
VAR DiscountResult =
SWITCH(
TRUE(),
SelectedFrame = "MTD", CALCULATE([VE - Discount % MTD]),
SelectedFrame = "YTD", CALCULATE([VE - Discount % FYTD]),
SelectedFrame = "MAT", CALCULATE([VE - Discount % MAT]),
SelectedFrame = "Date Range", CALCULATE([VE - Discount %]),
0
)
RETURN DiscountResult

common cause for disappearing values when sorting is the presence of blank values in the measure.

Switch - Customer TimeFrame - Discount % =
VAR SelectedFrame = SELECTEDVALUE('Slicer - TimeFrame'[Select Time Frame], "MTD")
VAR DiscountResult =
SWITCH(
TRUE(),
SelectedFrame = "MTD", COALESCE([VE - Discount % MTD], 0),
SelectedFrame = "YTD", COALESCE([VE - Discount % FYTD], 0),
SelectedFrame = "MAT", COALESCE([VE - Discount % MAT], 0),
SelectedFrame = "Date Range", COALESCE([VE - Discount %], 0),
0
)
RETURN DiscountResult

check with the above measure, hope it works in your scenerio