Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
nandukrishnavs
Community Champion
Community Champion

@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
🙂


Regards,
Nandu Krishna

View solution in original post

1 REPLY 1
nandukrishnavs
Community Champion
Community Champion

@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
🙂


Regards,
Nandu Krishna

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.