Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX WHERE clause equivalent multiple tables

Hi, tables are joined on partNumber.
I am trying to figure out how to get
hostHardware = (confighHost[Qty] * partsCost[Cost]) WHERE partsInterval[Interval] = "FIXED" && partsSuperCategory[superCategory] = "BASELINE"

I know that I can use a calculated column or query to get this, and I have created one large merged table to do it, but I am trying to learn DAX and get my head around table relationships etc.

This DOES work: hostHardware = sumx(confighost, RELATED(partsCost[Cost]) * configHost[Qty]) but I can not figure out how to get a WHERE.

In my mind, I need to create a table in memory of the 1st clause, then AND it with a table of the 2nd clause, then take the resulting partNumbers and using the innerjoin, multiply it by qty

 

 

I also went down this path: 

hostHardware = sumx(configHost,

FILTER(configHost,

AND (

FILTER(partsSuperCategory, partsSuperCategory[superCategory] = "BASELINE"),

FILTER(partsInterval, partsInterval[Interval] = "FIXED")

)

) * configHost[Qty]

)

 

Relationships

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    I think:

    hostHardware = sumx(FILTER(configHost,

    AND (

    FILTER(partsSuperCategory, partsSuperCategory[superCategory] = "BASELINE"),

    FILTER(partsInterval, partsInterval[Interval] = "FIXED")

    )

    ) * configHost[Qty]

    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks Greg but I get "Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2." which I can see is the case. If it give it a simple 2nd argument, like this

    hostHardware = sumx(FILTER(configHost,
    AND (
    FILTER(partsSuperCategory, partsSuperCategory[superCategory] = "BASELINE"),
    FILTER(partsInterval, partsInterval[Interval] = "FIXED")
    )), configHost[Qty] * 5
    )

    I get: 

    The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

     

    as a note, this measure is being created in the configHost table

     

    • TomMartens's avatar
      TomMartens
      Icon for Super User rankSuper User
      6 years ago

      Hey Anonymous ,

       

      I'm not sure, and maybe I'm missing something, but I guess this should work. Just use CALCULATETABLE, to apply additional filter inside your SUMX iterator, RELATED will still work, here is a sample:

      Measure 2 = 
SUMX(
    CALCULATETABLE(
        'Fact Sale'
        , 'Dimension City'[Sales Territory] = "External"
        , 'Dimension Customer'[Buying Group] = "Tailspin Toys"
    )
    , [Quantity] * RELATED('Dimension Stock Item'[Unit Price])
)

      Hopefully, this provides what you are looking for, at least a new idea.

       

      Regards,

      Tom

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks Tom, after substituting my table names, it works!

        Measure 2 =
        SUMX(
        CALCULATETABLE(
        configHost
        , partsSuperCategory[superCategory] = "BASESYSTEM"
        , partsInterval[Interval] = "FIXED"
        )
        , configHost[Qty] * RELATED(partsCost[Cost])
        )