Help build dax using SQL left join

[larx_mandel]

Hi community,

I have some tables related in my model, but I cannot find the correct DAX to group them. In SQL I have the following query which gives me the results. My example is a Sales tables, which has the product, each product belongs to a region, each region belong to a state. 

I need to obtain the sum of sales by state.

SELECT s.Name as State_Name , sum(nr_product_sold) as nr_prod_sold
FROM SALES sales
left join PRODUCT p on sales.id_product = p.id_product
left join REGION r on region.id_region = p.id_region
left join STATES s on s.id_state = r.id_state
group by s.Name

Thanks for your help.

[ryan_mayu · Accepted solution]

can we use merge queries in PQ, we can do the data transform like left join

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data?wt.mc_id=DP-M...

or you can provide some sample data and the expected output

[Anonymous · Accepted solution]

@ryan_mayu  Thanks for your contribution on this thread.

Hi @larx_mandel ,

@ryan_mayu  provided the method in Power Query Editor, here I have another method to help you achieve the requirement. Please follow the steps below to get it:

1. Assume you have the tables SALES, PRODUCT,REGION and STATES in your data model

2. Create a measure as below 

MEASURE =
VAR _statename =
    SELECTEDVALUE ( 'STATES'[Name] )
VAR _stateids =
    CALCULATETABLE (
        VALUES ( 'STATES'[id_state] ),
        FILTER ( 'STATES', 'STATES'[Name] = _statename )
    )
VAR _regionids =
    CALCULATETABLE (
        VALUES ( 'REGION'[id_region] ),
        FILTER ( 'REGION', 'REGION'[id_state] IN _stateids )
    )
VAR _productids =
    CALCULATETABLE (
        VALUES ( 'PRODUCT'[id_product] ),
        FILTER ( 'PRODUCT', 'PRODUCT'[id_region] IN _regionids )
    )
VAR _psold =
    CALCULATE (
        SUM ( 'SALES'[nr_product_sold] ),
        FILTER ( 'SALES', 'SALES'[id_product] IN _productids )
    )
RETURN
    _psold

3. Create table visual and apply the field 'STATES'[Name] and the above measure on it

Best Regards

[ryan_mayu · Accepted solution]

can we use merge queries in PQ, we can do the data transform like left join

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data?wt.mc_id=DP-M...

or you can provide some sample data and the expected output