Understanding DAX Query

Anonymous — Tue, 12 May 2020 08:06:13 GMT

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.

Re: Understanding DAX Query

nandukrishnavs — Tue, 12 May 2020 08:59:45 GMT

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 🙂

topic Understanding DAX Query in DAX Commands and Tips

Understanding DAX Query

Re: Understanding DAX Query