Joining Multiple Tables with subquery

[Astralpro20]

Hi,

I need to join 3 tables with subquery to count items in each status.

In SQL:

SELECT d.statusCode, d.statusDesc, COUNT(d.statusCode) as Total FROM tbItems a
    INNER JOIN tbStatusHistory b on a.itemid = b.itemid
        and b.statusid = (select top 1 statusid from statusHistory x
                   where x.itemid = a.itemid and x.status_date = (select max(status_date) from statusHistory y where y.itemid = x.itemid))
    INNER JOIN tbStatus d on d.statusCode = b.statusCode
WHERE START_DATE >= @dtfrom and START_DATE <= @dtto
GROUP BY d.statusCode, d.statusDesc, d.seqno
ORDER BY d.seqno

SQL Results:

My Chart using HighChart. I want to do this in Power BI.

My Chart using HighChart. I want to do this in Power BI

Thank you in advance. 

[kentyler]

Looking at your SQL you would end up with 2 tables in Power BI

tblItems

and

tblStatusHistory
the value from the status lookup table with the status codes should be be merged into tlbStatusHistory before you load the data. PowerBI does not mind redundant fields and it is common practice to merge all lookup tables into the transactional tables.

It looks like you want to work with only the most recent record for an item in status history, so you should do a view in sql server that selects the max date and returns only those records. Then load the view into power bi rather than the original table. The rule is to do as much work as possible in sql server... power bi can be complicated enough the way it is.

Then you will end up with your LatestStatusHistory view as your fact table and your tblItem as your dimension table. You should have a 1 to many relationship between items and status history. 
Data models in Power BI should be star schemas, just like in data warehousing. It makes all your DAX MUCH SIMPLER.

This is a big area to talk about. If you'd like to discuss it more send me a day and time and we can do a screen share.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.