March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi everyone,
I have 2 different columns which are filters: Month and Quarter.
I also have a card which I need calculation depending which filter is applied. For example:
I have 2 numeric columns (Amount1 and Amount2)
If I do a month filter, so take the value Amount1 and substract Amount2 of the month.
If I do a quarter filter, I take the value of SUM(Amount1) and need substract the last value of the Quarter for Amount 2.
Month - Quarter - Amount1 - Amount2
01 1 100 100
02 1 153 800
03 1 123 400
04 2 543 600
05 2 234 300
06 2 123 200
If I filter Month 05, so 234 - 300
If I filter Q2, so 543+234+123 - 200
If I filter Month 01, so 100 - 100
If I filter Q1, so 100+153+123 - 400
I guess is a IF but depending which table/column is filtered and active, but I cannot find nothing.
Thanks!!
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Expected result measure: =
SWITCH (
TRUE (),
HASONEFILTER ( Data[Month] ), SUM ( Data[AmountOne] ) - SUM ( Data[AmountTwo] ),
HASONEFILTER ( Data[Quarter] ),
SUM ( Data[AmountOne] )
- CALCULATE (
SUM ( Data[AmountTwo] ),
FILTER ( VALUES ( Data[Month] ), Data[Month] = MAX ( Data[Month] ) )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Expected result measure: =
SWITCH (
TRUE (),
HASONEFILTER ( Data[Month] ), SUM ( Data[AmountOne] ) - SUM ( Data[AmountTwo] ),
HASONEFILTER ( Data[Quarter] ),
SUM ( Data[AmountOne] )
- CALCULATE (
SUM ( Data[AmountTwo] ),
FILTER ( VALUES ( Data[Month] ), Data[Month] = MAX ( Data[Month] ) )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
114 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
168 | |
116 | |
63 | |
57 | |
50 |