Forum Discussion

SriGaG1's avatar
SriGaG1
Icon for Helper II rankHelper II
3 years ago
Solved

Power BI ALLEXCEPT function is not working with slicer switch functions

Hi There,

 

I am trying to build a calculaion to identify the total sum of values of product code. In tableau, we do use, {FIXED [PRODUCTCODE]: [SORT_SLICER_VALUE]}. But in power BI, I have wrote the same like below

 

output = CALCULATE([SORT_SLICER_VALUE],ALLEXCEPT('ProductMaster',' ProductMaster'[PRODUCTCODE]))

 

SORT_SLICER_VALUE = SWITCH(SELECTEDVALUE('Slicers Des'[Slicer Des]),
"Price variance/ kg Original - Actual", [Price variance/kg Original - Actual],
"Price variance/ ctn Original - Actual", [Price variance/ctn Original - Actual])

 

But this calculation returns the same disaggregated value in the [SORT_SLICER_VALUE] measure. As an example,

 

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    3 years ago

    SriGaG1 

    If you could mention what the desired outcome , it helps to give a solution

    • SriGaG1's avatar
      SriGaG1
      Icon for Helper II rankHelper II
      3 years ago

      VijayP  thank you for the comment. Actually the required outcome is mentioned in the table i have provided. I will paste that one here as well. Pls refer the final column.

       

      Thanks

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    3 years ago

    SriGaG1 
    SELECTEDVALUE('Slicers Des'[Slicer Des]) , this is what you have used to impact on allexcept but in the table it is not appearing, have you used it as a slicer externally! 

    • SriGaG1's avatar
      SriGaG1
      Icon for Helper II rankHelper II
      3 years ago

      VijayP  Exactly. I have created an extrernal table. Based on that table the slicer is determined. So is there any work around to do this ?

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi SriGaG1 ,

     

    According to your description, I have created a simple sample:

    It works fine in my side:

    Measure = CALCULATE([SORT_SLICER_VALUE],ALLEXCEPT(ProductMaster,ProductMaster[PRODUCTCODE]))

     

     

    If something is not quite the same as yours, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Refer to:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

    Best Regards,

    Jianbo Li

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