topic Re: If Condition Performance Issues in DAX Commands and Tips

If Condition Performance Issues

rajk — Thu, 21 Mar 2024 15:16:39 GMT

I had to use a filter with IF condition. It is not performing and report fails. With out the filter the report runs fine.

Filter = if(and(SELECTEDVALUE('Table [Column])>=DATE(YEAR(TODAY()), MONTH(TODAY())-3, 1) , SELECTEDVALUE('Table [Column])<=TODAY()-1),"Yes","No")

Basically We wanted last three months and day 1 of the month and Yesterday dynamically.

If we look at today it would be 12/1/2023 - 3/20/2024.

Any Help please..!

Re: If Condition Performance Issues

gmsamborn — Thu, 21 Mar 2024 16:56:04 GMT

Hi rajk

Would a measure like this help?

Filter = IF( SELECTEDVALUE( 'Sheet1'[Date] ) >= EOMONTH( TODAY(), - 4 ) + 1 && SELECTEDVALUE( 'Sheet1'[Date] ) <= TODAY() - 1, "Yes", "No" )

Let me know if you have any questions.

test IF perf.pbix

Re: If Condition Performance Issues

Anonymous — Mon, 25 Mar 2024 02:00:28 GMT

Hi rajk ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: If Condition Performance Issues

rajk — Wed, 27 Mar 2024 13:54:18 GMT

I had same thing but report fails. Even I added dates manually like 12/1/2023 and 3/26/2023, .. But report fails after running for some time. unbale to figure out where the performance issue is. Or an alternative for IF which is better performing.

Re: If Condition Performance Issues

rajk — Wed, 27 Mar 2024 13:55:32 GMT

We are on Fabric with schematic model.

Re: If Condition Performance Issues

Anonymous — Thu, 28 Mar 2024 06:34:15 GMT

Hi rajk,

Perhaps you can try to use IN operator to check if the current date are included in specific ranges:

formula = VAR currDate = MAX ( 'Table1'[Column] ) VAR _range = CALENDAR ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 3, 1 ), TODAY () - 1 ) RETURN IF ( currDate IN _range, "Yes", "No" )

The IN operator in DAX - SQLBI

If the aboe not help, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,
Xiaoxin Sheng