If Condition Performance Issues

[rajk]

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..!

[Anonymous]

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

[rajk]

We are on Fabric with schematic model.

[Anonymous]

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

[gmsamborn]

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

[rajk]

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.