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
Community 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] ) )

 

 

 

  • DanVelus's avatar
    DanVelus
    New Member
    4 years ago

    Hey, @smpa01 Thank you so much for both of your responses. I tried all your solutions, I only getting BLANK. I am not getting the expected answer. Here are the some of things I tried:

    Table name is 'groups'; Columns: user_id & zone_id
    Users_inBothGroup =
    VAR _select = {123, 456}
    VAR _count1 =
    COUNTX (_select, [Value])
    VAR _count2 =
    CALCULATE(
    COUNT(groups[user_id]),
    groups[zone_id] IN _select,
    ALLEXCEPT(groups, groups[user_id])

    )
    RETURN
    IF( _count1 = _count2, MAX(groups[user_id]) )

    The Result: BLANK


    VAR _a =
    CALCULATE(
    MAX(groups[user_id]),
    FILTER(VALUES(groups[zone_id]), groups[zone_id] = 123)
    )
    RETURN
    CALCULATE(
    MAX(groups[user_id]),
    FILTER(groups, groups[user_id] = _a && groups[zone_id] = 456)
    )

    Result: BLANK

    I am not sure what I am doing wrong.

    Please help