Forum Discussion
Understanding DAX Query
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.
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 🙂
1 Reply
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 🙂