Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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 🙂
@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 🙂
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |