Forum Discussion

DanVelus's avatar
DanVelus
New Member
4 years ago
Solved

Nested DAX query with filter conditions

Hi there, I am fairly new to DAX, I would really appreciate your assistance, please. I am trying to filter out the number of customers that belong to both zones. The table looks like this: Table...
DAX
  • Anonymous's avatar
    Anonymous
    4 years ago

    hi DanVelus ,

     

    I try to understand the model you are talking about and hope to get the result you want in the end.

    Please create two new table as follows firstly:

    Then, create a measure:

    Result should look like this:

    You can also create a new measure as follows:

    Result should look like this:

    Hope it helps!

     

    Best regards,

    Community Support Team  CGao

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

DanVelus  equivalent DAX Measure is this

 

Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Customer_id] ),
        FILTER ( VALUES ( 'Table'[Zone_id] ), 'Table'[Zone_id] = "A" )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Customer_id] ),
        FILTER ( 'Table', 'Table'[Customer_id] = _a && 'Table'[Zone_id] = "B" )
    )

 

 

 

a more dynamic approach would be following

Measure2 =
VAR _select = { "A", "B" }
VAR _count1 =
    COUNTX ( _select, [Value] )
VAR _count2 =
    CALCULATE (
        COUNT ( 'Table'[Customer_id] ),
        'Table'[Zone_id] IN _select,
        ALLEXCEPT ( 'Table', 'Table'[Customer_id] )
    )
RETURN
    IF ( _count1 = _count2, MAX ( 'Table'[Customer_id] ) )