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

[Jeff_Ng]

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.

Please help. 

[v-qiuyu-msft]

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 

[Jeff_Ng]

hi @v-qiuyu-msft ,

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")

[v-qiuyu-msft · Accepted solution]

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 

[Jeff_Ng]

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

Hope it works. Any problem please let me know. 

[v-qiuyu-msft]

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

[Jeff_Ng]

Hi @v-qiuyu-msft ,

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?

[v-qiuyu-msft]

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