cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## equal "=" operator didn't seem to work on calculate and filter functions

I have problem to apply more than one filter (<filter2>....) to my FILTER and CALCULATE functions using the equal "=" operator. They only take the first filter but not the subsequence filters.

To make it work I had to go opposite way using multiple no equal "<>" operator which make my programme very long. Instead of saying I want to keep 2 things out of 20, now I have to say remove 18 things out of 20.

CALCULATE(<expression>,<filter1>,<filter2>…)

FILTER(<table>,<filter1> && <filter2>) or FILTER(<table>, AND(<filter1>, <filter2>)

I working with the latest April 2019 Power BI Desktop version. I am still learning DAX but logic tell me if it can take multiple "<>" conditions it should take multiple "=" conditions and other multiple of other operators.

1 ACCEPTED SOLUTION
Community Support

Hi @Jeff_Ng

The filter conditions in CALCULATE(<expression>,<filter1>,<filter2>…) are performed with AND operation.

You can modify the Calculate_2 as below:

Calculate_2 = var t=SUMMARIZE(FILTER(ALL('Table1'),Table1[Colour]="Red"|| Table1[Colour]="Blue"),Table1[Colour],"total",[SumX_1])
return SUMX(t,[total])

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support

Hi @Jeff_Ng

I have modified the measure below:

Calculate_2 = CALCULATE([SumX_1],FILTER('Table1',Table1[Colour]="Red"|| Table1[Colour]="Blue"))

SumX_2 =
SUMX(
FILTER(Table1,Table1[Colour]="Red" || Table1[Colour]="Blue"),
Table1[Num1]*Table1[Num2]
)

SumX_3 =
SUMX(
FILTER(Table1, OR(Table1[Colour]="Red", Table1[Colour]="Blue")),
Table1[Num1]*Table1[Num2]
)

Please check if they meet your requirements.

Best Regards,
Qiuyun Yu
Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Not quite. I,m expecting Calculate_2 is equal to Calculate_4 which is 34 in all rows. Similarly SumX_2 = SumX_4, and SumX_3 = SumX_5.

In fact my concern is not about the results. I want explaination why multiple filters with "=" operators not working in Case 1 but multiple filters with "<>" operators worked in the Case 2.

Sybtax for CALCULATE(<expression>,<filter1>,<filter2>…)

Case 1, Calculate_2 = CALCULATE([SumX_1],Table1[Colour]="Red", Table1[Colour]="Blue")
Case 2, Calculate_3 = CALCULATE([SumX_1],Table1[Colour]<>"Red", Table1[Colour]<>"Blue")

Community Support

Hi @Jeff_Ng

The filter conditions in CALCULATE(<expression>,<filter1>,<filter2>…) are performed with AND operation.

You can modify the Calculate_2 as below:

Calculate_2 = var t=SUMMARIZE(FILTER(ALL('Table1'),Table1[Colour]="Red"|| Table1[Colour]="Blue"),Table1[Colour],"total",[SumX_1])
return SUMX(t,[total])

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

https://1drv.ms/u/s!AjfbxnAiqgTKoBLfbs757sV5Gup6

Hope it works. Any problem please let me know.

Community Support

Hi @Jeff_Ng

If the report doesn't contain any sensitive data, you can upload it to your OneDrive and paste the share link here.

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thanks for taking time to response. Attached is the pbix file with ficticius data with some of my measures explaning the problems I mentioned above.

Please explain why "=" operator is not working in measures

• Calculate_2
• SumX_2
• SumX_3

ohh..How do I attach file?

Community Support

Hi @Jeff_Ng

Is it possible for you to share a pbix file to clarify the issue? Please remove sensitive data in the report before you share it.

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.