Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Advocate IV
Advocate IV

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


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 = 
    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




Super User
Super User

@praveenpasila , I have taken a little bit different approch


Check the attached file

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

Hi @praveenpasila ,


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.

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

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


Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.