IF/Switch function not working in DirectQuery Mode

[Apsawhney]

Hi Team,

I have a report in which I want the dataset to refresh every hour. Due to the 8 refresh limit in Import mode I had to use DirectQuery Mode. But now the IF function is not working properly. 

My IF function = IF(MAXX(Aggregation Count)<MAXX(Threshold),0,1)

This function works fine in Import Query mode. But in Direct Query mode its only giving 0 or NULL instead of 0 or 1. Can you advise here. 

[v-xicai]

Hi @Apsawhney ,

From the Microsoft official document DAX formula compatibility in DirectQuery mode, we know that function IF and SWITCH are supported in all DAX formulas, including measures, queries, calculated columns, row level security. While the MAXX function is only supported in measure and query formulas.

By my test in the latest version 2.74.5619.862 of Power BI Desktop, the function IF and SWITCH work well when using DirectQuery connect mode.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Apsawhney]

Doesn't work for me.

My PBI desktop version -  2.74.5619.862

Not working

[Apsawhney · Accepted solution]

OK, so I seem to have found the solution which is even wierder. If I add a 3rd paramemter in the IF function then that gives "False" result properly. 

Working IF Function - 

IF(MAX(Registration)<MAX(Threshold),0,0,1)

This gives 0 if true and 1 if false.

Not working function - 

IF(MAX(Registration)<MAX(Threshold),0,1)

This gives 0 if true and BLANK if false.

NO idea why its doing this. Tried it on couple of machines and same result on all.