Understanding DAX Query

[Anonymous]

Hi All, 

Hope you can help. I am trying to understand a DAX query that has been developed so that I can maniuplate. Below is a copy of the query:

ValueCOL =

CALCULATE(
CALCULATE(MAX(Table 1[Value]),

FILTER(Table1,
IF(ISFILTERED('Table P'[P Level 2]),

Table 1[FChannel] IN VALUES('TableC'[FChannel])
&& Table1[P Level 1] IN VALUES('TableP'[P Level 1])
&& Table1[P Level 2] IN VALUES('TableP'[P Level 2]),

IF(ISFILTERED('Table P'[P Level 1]),
Table 1[F Channel] IN VALUES('Table C'[F Channel])
&& Table 1 [P Level 1] IN VALUES('Table P'[P Level 1]),
IF(ISFILTERED('Table C'[FChannel]),Table 1[ FChannel] IN VALUES('Table C'[F Channel]), MAX(Table 1[Value])))))))

Any help appreciated.

[nandukrishnavs · Accepted solution]

@Anonymous 

ValueCOL =
CALCULATE (
    CALCULATE (
        //    finding the max value in Value column
        MAX ( 'Table1'[Value] ),
        //  filtering the Table1      
        FILTER (
            'Table1',
            //   If the column P Level 2 is filtered then FChannel  in Table1 = FChannel in TableC and next condition       
            IF (
                ISFILTERED ( 'Table P'[P Level 2] ),
                'Table1'[FChannel]
                    IN VALUES ( 'TableC'[FChannel] )
                    && 'Table1'[P Level 1]
                        IN VALUES ( 'TableP'[P Level 1] )
                    && 'Table1'[P Level 2]
                        IN VALUES ( 'TableP'[P Level 2] ),
                // else applying another filter condition                        
                IF (
                    ISFILTERED ( 'Table P'[P Level 1] ),
                    'Table1'[F Channel]
                        IN VALUES ( 'Table C'[F Channel] )
                        && 'Table1'[P Level 1]
                            IN VALUES ( 'Table P'[P Level 1] ),
                    //else another
                    IF (
                        ISFILTERED ( 'Table C'[FChannel] ),
                        'Table1'[ FChannel]
                            IN VALUES ( 'Table C'[F Channel] ),
                        MAX ( 'Table1'[Value] )
                    )
                )
            )
        )
    )
)

https://docs.microsoft.com/en-us/dax/if-function-dax

https://docs.microsoft.com/en-us/dax/filter-functions-dax

https://docs.microsoft.com/en-us/dax/isfiltered-function-dax

https://docs.microsoft.com/en-us/dax/values-function-dax

https://docs.microsoft.com/en-us/dax/in-operator-containsrow-function

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂