Please help to translate SQL join with sub query to DAX.

[Datawhisperer73]

Hello folks, 

Would it be possible to help to translate this SQL statement to DAX.

I was able to translate this SQL query to M with Power query, but I am looking to create a dynamic table in DAX instead of a dataset. Thank you!

This is the SQL query:

select
dr.Origin,
dr.Destination,
dr.Region,
dr.SubRegion,
dr.HaulType
from Dim_Region dr
inner join
(
select
Origin,
Destination,
MAX(DssStartDT) as DssStartDT,
MAX(DssVersion) as DssVersion
from Dim_Region
group by
Origin,
Destination
) gb
on
dr.Origin = gb.Origin
and dr.Destination = gb.Destination
and dr.DssStartDT = gb.DssStartDT
and dr.DssVersion = gb.DssVersion

I did create this dynamic table in DAX but the output is not reporting right record count, beside it take time load data.

RegionTable =
VAR RegionGroupBY =
SUMMARIZE (
DIM_REGION,
DIM_REGION[ORIGIN],
DIM_REGION[DESTINATION],
"DSSSTARTDT", SUMX(DIM_REGION, DIM_REGION[DSSSTARTDT]),
"DSSVERSION", SUMX(DIM_REGION, DIM_REGION[DSSVERSION])
)
RETURN

FILTER(
GENERATEALL(
SELECTCOLUMNS(
DIM_REGION,
"Origin", DIM_REGION[ORIGIN],
"Destination", DIM_REGION[DESTINATION],
"Region", DIM_REGION[REGION],
"SubRegion", DIM_REGION[SUBREGION],
"HaulType", DIM_REGION[HAULTYPE],
"DSSSTARTDT", DIM_REGION[DSSSTARTDT],
"DSSVERSION", DIM_REGION[DSSVERSION]
),
SELECTCOLUMNS(
RegionGroupBY,
"Origin1", [ORIGIN],
"Destination1", [DESTINATION],
"DSSSTARTDT1", [DSSSTARTDT],
"DSSVERSION1", [DSSVERSION]
)
),
[Origin] = [Origin1]
&& [Destination] = [Destination1]
&& [DSSSTARTDT] = [DSSSTARTDT1]
&& [DSSVERSION] = [DSSVERSION1]
)

[v-janeyg-msft · Accepted solution]

Hi, @Datawhisperer73 

You can try like this:

x =
VAR t1 =
    SELECTCOLUMNS (
        Dim_Region,
        "Origin", Dim_Region[Origin],
        "Destination", Dim_Region[Destination],
        "Region", Dim_Region[Region],
        "SubRegion", Dim_Region[SubRegion],
        "HaulType", Dim_Region[HaulType],
        "DSSSTARTDT", Dim_Region[DSSSTARTDT],
        "DSSVERSION", Dim_Region[DSSVERSION]
    )
VAR t2 =
    SELECTCOLUMNS (
        SUMMARIZE (
            Dim_Region,
            Dim_Region[Origin],
            Dim_Region[Destination],
            "DSSSTARTDT", MAX ( Dim_Region[DSSSTARTDT] ),
            "DSSVERSION", MAX ( Dim_Region[DSSVERSION] )
        ),
        "Origin1", [Origin],
        "Destination1", [Destination],
        "DSSSTARTDT1", [DSSSTARTDT],
        "DSSVERSION1", [DSSVERSION]
    )
RETURN
    GENERATE (
        t1,
        VAR a = [Origin]
        VAR b = [Destination]
        VAR c = [DSSSTARTDT]
        VAR d = [DSSVERSION]
        RETURN
            FILTER (
                t2,
                [Origin1] = a
                    && [Destination1] = b
                    && [DSSSTARTDT1] = c
                    && [DSSVERSION1] = d
            )
    )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Datawhisperer73]

@v-janeyg-msft  You have absolutely resolved my issue and your solution works as expected!

Great Thanks!

[amitchandak]

@Datawhisperer73 , suggesting measure for visual

Create two dates like

DssStartDT_max = calculate(MAX(Dim_Region[DssStartDT]), allexcept(Dim_Region,Dim_Region[Origin],Dim_Region[Origin]))

DssVersion_max = calculate(MAX(Dim_Region[DssVersion]), allexcept(Dim_Region,Dim_Region[Origin],Dim_Region[Origin]))

or

DssStartDT_max = calculate(MAX(Dim_Region[DssStartDT]), filter(allselected(Dim_Region),Dim_Region[Origin] = max(Dim_Region[Origin]) && Dim_Region[Origin] = Max(Dim_Region[Origin])))

DssVersion_max = calculate(MAX(Dim_Region[DssVersion]), filter(allselected(Dim_Region),Dim_Region[Origin] = max(Dim_Region[Origin]) && Dim_Region[Origin] = Max(Dim_Region[Origin])))

Then create final meausre to be used in visual, that will filter on max date

calculate(countrows(Dim_Region), filter(Dim_Region , Dim_Region[DssStartDT] =[DssStartDT_max] && Dim_Region[DssVersion] = [DssVersion_max]))

or

calculate(countrows(Dim_Region), filter(allselected(Dim_Region) , Dim_Region[DssStartDT] =[DssStartDT_max] && Dim_Region[DssVersion] = [DssVersion_max]))

[Datawhisperer73]

@amitchandak  Thank you so much for your help!

[Datawhisperer73]

Hi Amitchandak,

I am not looking to create measures, I am looking to create a full dynamic table using DAX!

[Datawhisperer73]

I want to translate the following SQL query to Power BI dynamic table using DAX and not using Power query please!

select
dr.Origin,
dr.Destination,
dr.Region,
dr.SubRegion,
dr.HaulType
from Dim_Region dr
inner join
(
select
Origin,
Destination,
MAX(DssStartDT) as DssStartDT,
MAX(DssVersion) as DssVersion
from Dim_Region
group by
Origin,
Destination
) gb
on
dr.Origin = gb.Origin
and dr.Destination = gb.Destination
and dr.DssStartDT = gb.DssStartDT
and dr.DssVersion = gb.DssVersion

[amitchandak]

@Datawhisperer73 , My assumption is that you are keeping a max record of these two tables. There no dynamic table. Means slicer will not change table

Create a new table like

filter(
AddColumn(Dim_Region, "Rank1", rankx(filter(Dim_Region, [Origin] = earlier([Origin]) && [Origin] = earlier([Origin])), [DssStartDT],asc, ) ,
"Rank2", rankx(filter(Dim_Region, [Origin] = earlier([Origin]) && [Origin] = earlier([Origin])), [DssVersion],asc, )
), Rank1 = 1 && Rank2 =1)

also refer: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/