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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mizaskun
Helper II
Helper II

Modify measures depending on filters

Good morning,

 

I have a very simple model:

 

UserTypeAmount
AZ100
AZ200
AY50

 

I created the following measure:

TotalByUser = calculate( sum(Table[Amount]), allexcept(Table, Table[User]))

 

I created a very simple report:

UserTypeAmountTotalByUser
AZ300350
AY50350

 

And when I filter the report by Type = Z, y get the following:

UserTypeAmountTotalByUser
AZ300350

 

What I would need is to filter the measure the same way the report is being filtered. The result should be:

UserTypeAmountTotalByUser
AZ300300

 

And if I unresume the Amount field, the result should look like this applying the filter:

UserTypeAmountTotalByUser
AZ100300
AZ200

300

 

And like this if I do not apply it:

UserTypeAmountTotalByUser
AZ100350
AZ200350
AY50350

 

Is it possible to achieve this? Does anybody know how to do it? 

 

Thank you so much!

1 ACCEPTED SOLUTION

TotalByUser =
CALCULATE(
    SUM( Table[Amount] ),
    VALUES( Table[User] ),
    ALLSELECTED( 'Table' )
)

It might be that the above does what you want...

View solution in original post

5 REPLIES 5
daxer-almighty
Solution Sage
Solution Sage

@mizaskun 

 

If your model is much different from what you show here, then it'll be very hard to give you a good solution.

 

But even if you do show the right model, I'd recommend you first read this: Using ALLEXCEPT versus ALL and VALUES - SQLBI

 

It may explain why you see what you see.

Thank you so much. I will. 

amitchandak
Super User
Super User

@mizaskun , Try a measure like

 

sumx(values(Table[User]) ,if(isfiltered(Table[Type]) , calculate(sum(Table[Amount])), calculate( sum(Table[Amount]), allexcept(Table, Table[User]))))

Good morning,

 

Thank you for your answer! The model actually have much more fields through which can be filtered. The table I explained was a simplification to make it easier to understand what I need. There should be a way to achieve that behavour. 

 

Thank you so much! 

TotalByUser =
CALCULATE(
    SUM( Table[Amount] ),
    VALUES( Table[User] ),
    ALLSELECTED( 'Table' )
)

It might be that the above does what you want...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors