Forum Discussion

rajk's avatar
rajk
Regular Visitor
2 years ago

If Condition Performance Issues

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

5 Replies

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    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's avatar
      rajk
      Regular Visitor
      2 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      rajk
      Regular Visitor
      2 years ago

      We are on Fabric with schematic model.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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