Forum Discussion

AndySmith's avatar
AndySmith
Helper III
1 year ago
Solved

SWITCH measures - sorting issue

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

6 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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

  • AndySmith's avatar
    AndySmith
    Helper III
    1 year ago

    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's avatar
      rajendraongole1
      Super User
      1 year ago

      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

  • AndySmith's avatar
    AndySmith
    Helper III
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        AndySmith
        Helper III
        1 year ago

        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.