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, though I still don't know how to do it in DAX. 

 

I have two tables (Stock and Sold Items) wich are related trough anohter table with ItemID's. I want to see thow many radio's have been sold in the last 1,5 year (Date in Sold Items table). There are 4 types of Categorys (in Stock table) that have radio's. I guess the code in SQL would be something like:

 

SELECT COUNT (Order_ID) AS Radio_Count

FROM so Sold_Items

JOIN i Item_ID 

ON so.Item_ID=i.Item_ID

JOIN s Stock

ON i.Item_ID=s.Item_ID

WHERE s.Category = "cat1"

OR "cat2"

OR "cat3"

OR "cat4"

 

Eventually I would like to see something like this:

 

 

Please help me!? 

  • v-sihou-msft's avatar
    v-sihou-msft
    8 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,

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Import your 3 tables and build the relationships in the relationship editor.

     

    Then, create a column or measure that goes something like this:

     

    Measure = CALCULATE(COUNT([Column]),RELATED(Table))

    Really tough to be more specific without sample data.

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 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,