CODEWITHSUNDEEP
filterdaxcalculated-columns
Hell-1931
Hell-1931

Reputation: 499

Creating calculated table with MAX and MIN dates in DAX Power BI

I have the following table, imported in Power BI - QOL_Exp (see screenshot example below)

I need to create a calculated table which will filter out values, where Rating = 999 and, at the same time, will pick only the highest and the lowest Date values from Date column, based on ClientID (see highlighted grey and peach colored areas).

I highlighted in red font - the values that I expect to see in my calculated table

For example, for ClientID = 3052 I will need the records where Date = 11/20/2020 (lowest date for this ClientID) and Date = 5/17/2021 (highest date for this ClientID)

For ClientID = 2666 I will not need the record where Rating = 999 (one of the conditions)

I managed to filter out (to exclude Rating = 999) but struggling with including only Max and MIN date in the new calculated table

enter image description here

This is my DAX:

      QOL = CALCULATETABLE(QOL_Exp, QOL_Exp[Rating]<>999)

How should I modify it in order to only leave Max(Date) and Min(Date) records, based on ClientID?

UPD: Based on the answer given, slightly updated (see below):

      QOL = 
         FILTER (QOL_Exp,  QOL_Exp[Rating] <> 999 
           && 
              (( QOL_Exp[Date] = CALCULATE (MIN ( QOL_Exp[Date] ), 
                  ALLEXCEPT(QOL_Exp,QOL_Exp[ClientID])))
              || QOL_Exp[Date] = CALCULATE (MAX ( QOL_Exp[Date] ), 
                   ALLEXCEPT(QOL_Exp, QOL_Exp[ClientID]))))

Upvotes: 0

Views: 2608

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9052

QOL =
FILTER (
    QOL_Exp,
    QOL_Exp[Rating] <> 999
        && (
            QOL_Exp[Date]
                = CALCULATE (
                    MIN ( QOL_Exp[Date] ),
                    FILTER (
                        QOL_Exp,
                        QOL_Exp[Rating] <> 999
                            && QOL_Exp[ClientID] = EARLIER ( QOL_Exp[ClientID] )
                    )
                )
                || QOL_Exp[Date]
                    = CALCULATE (
                        MAX ( QOL_Exp[Date] ),
                        FILTER (
                            QOL_Exp,
                            QOL_Exp[Rating] <> 999
                                && QOL_Exp[ClientID] = EARLIER ( QOL_Exp[ClientID] )
                        )
                    )
        )
)

Upvotes: 0

Related Questions