Count, Where and Join

[zoemostert]

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 · Accepted solution]

@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]

Thank u! That worked perfectly 🙂

[Greg_Deckler]

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.