Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Help on Treat As calculation

Hi All,

 

I am raising this as a new thread my actual problem is solved  which is the below one

 

https://community.powerbi.com/t5/Desktop/Assign-different-value/m-p/2903454#M997567

 

Basically I am trying to show junk values in header apart from selected value in slicer

 

I was suggested to use use below measure

 

Measure = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( DiscUsers[Type] ) = "U",
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            KEEPFILTERS ( TREATAS ( VALUES ( DiscUsers[User] ), 'Table'[User] ) )
        ),
    SELECTEDVALUE ( DiscUsers[User] ) <> SELECTEDVALUE ( 'Table'[User] ),
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            TREATAS ( VALUES ( DiscUsers[User] ), 'Table'[User] )
        )
)

 

when I am trying to use other slicers like date slicer it is giving wrong results tried using all, all except but still not working.

 

If I use rmovefilters its working fine but user wants to check only specific period data so remove filter wont help

 

first part of calculation is working fine but when I trying to add filter in the second part it is giving wrong results

 

Can someone please suggest.

 

Thanks in advance

 

 

 

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi amitchandak ,

       

      My data is slighthly different since you are using user1,2... substitute is working fine but my data is as below

      so I used direct text which is replacing fine

      Customer = ADDCOLUMNS(CROSSJOIN(SELECTCOLUMNS( DISTINCT('Table'[Customer]), "User", [Customer] ), DISTINCT('Table'[Customer])), "Disp Customer" , if([Customer] =[User] , [Customer] , "B"))

      but since everything is assigned with same value which is of no use can we use index or something similar instead of text "B" so we get the desired o/p. please suggest

      Thanks in advance

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you amitchandak for your suggestion its working fine. Since I have 30-35 customers I manually created a source file for customer table and used it and its giving me desired O/P one last ask will it be possible to assign 1 and 0 

      if selected value then 1 else 0 cause since I have so many customers it is hard to identify the selected name so if we can assign 1 and 0. we can use conditional formating and highlight the selected Customer

      Thank you for your help

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

        Hi Anonymous ,

         

        You can modify the return value of the conditional statement of the IF function.

        If your problem has been solved, please mark the helpful answer above as a solution and then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

         

        Best Regards,
        Community Support Team _ xiaosun

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you so much amitchandak I will try your approach and will let you know how it worked