Forum Discussion

zoemostert's avatar
zoemostert
Helper I
9 years ago
Solved

Count, Where and Join

I've been trying a lot of things to fix my problem and searched the entire website, but couldn't figure out how to fix it.    So I kinda know SQL and I believe that my query is very simple in SQL, ...
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    zoemostert

     

    I assume you already build the relationships between Sold_Items, Item_ID and Stock tables on "XXX_ID" column. Then you just need to create a measure to get the count calculation, and specify correct filter context to achieve where clause in DAX.

     

    =
CALCULATE (
    COUNT ( Sold_Items[Order_ID] ),
    FILTER (
        Sold_Items,
        RELATED ( Stock[Category] ) = "cat1"
            || RELATED ( Stock[Category] ) = "cat2"
            || RELATED ( Stock[Category] ) = "cat3"
    )
)

    For more details, please refer to article below:

     

    From SQL to DAX: Filtering Data

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

zoemostert

 

I assume you already build the relationships between Sold_Items, Item_ID and Stock tables on "XXX_ID" column. Then you just need to create a measure to get the count calculation, and specify correct filter context to achieve where clause in DAX.

 

=
CALCULATE (
    COUNT ( Sold_Items[Order_ID] ),
    FILTER (
        Sold_Items,
        RELATED ( Stock[Category] ) = "cat1"
            || RELATED ( Stock[Category] ) = "cat2"
            || RELATED ( Stock[Category] ) = "cat3"
    )
)

For more details, please refer to article below:

 

From SQL to DAX: Filtering Data

 

Regards,

  • zoemostert's avatar
    zoemostert
    Helper I
    8 years ago

    Thank u! That worked perfectly :)