Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
It's unclear to me how I can use FILTER() in a CALCULATE() function, and apply an AND / OR / NOT logic.
Could you please refer to an existing help page or give examples here below? Thanks.
Solved! Go to Solution.
I’ll give an example as below. Assuming we have a table like below.
If we want to get the total sales of China on July, we can use following formula.
SalesFromChina_July = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, AND ( Table1[Country] = "China", MONTH ( Table1[Date] ) = 7 ) ) )
If we want to get the total sales of China and USA on July and August, we can use following formula.
SalesFromChina_OR_USA = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, OR ( Table1[Country] = "China", Table1[Country] = "USA" ) ) )
If we want to get the total sales of India on July and August, we can use following formula.
SalesFromIndia = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, NOT ( OR ( Table1[Country] = "China", Table1[Country] = "USA" ) ) ) )
Best Regards,
Herbert
I’ll give an example as below. Assuming we have a table like below.
If we want to get the total sales of China on July, we can use following formula.
SalesFromChina_July = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, AND ( Table1[Country] = "China", MONTH ( Table1[Date] ) = 7 ) ) )
If we want to get the total sales of China and USA on July and August, we can use following formula.
SalesFromChina_OR_USA = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, OR ( Table1[Country] = "China", Table1[Country] = "USA" ) ) )
If we want to get the total sales of India on July and August, we can use following formula.
SalesFromIndia = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, NOT ( OR ( Table1[Country] = "China", Table1[Country] = "USA" ) ) ) )
Best Regards,
Herbert
This is very useful. However, I am trying to apply different targets to different months based on last years totals. So if month is September then add 70% to last years total, if month is October then 80 % to last year etc, then the months after that should be the same as last year. I have looked everywhere and cannot find the logic to do this. Any help would be great
Thanks. This is very clear.
To do an AND, you should create 2 filter statements, one for each.
=CALCULATE([measure],filter(),filter())
for an OR, do it like this
=CALCULATE([measure],filter(table,table[col1] = "X" || table[col2] = "Y"))
note this is not overly efficient, but it is how you do it. (working on 2 columns at once can be inefficient.
for NOT, you can use teh NOT() function around your tests
Hope that helps
User | Count |
---|---|
83 | |
69 | |
68 | |
65 | |
54 |
User | Count |
---|---|
93 | |
91 | |
91 | |
76 | |
70 |