Merge Vs Natural Join

[dejadpower]

Hi All,

I have 3 differrent tables , all are connected with builtin relationships

to achieve my desired output , I created a dax table

Result = SELECTCOLUMNS((NATURALINNERJOIN(BookingsWO,Resources)),"WO",BookingsWO[Work Order],"DESC",BookingsWO[Work Order Summary],"TYPE",Resources[Category],"NAME",Resources[Name],"START",BookingsWO[Start Date],"END",BookingsWO[End Date],"JOB",BookingsWO[JobNo],"CUSTOMER",BookingsWO[Service Account],
"MASON",if(Resources[Category]="MASON",1,0),
"PAINTER",if(Resources[Category]="PAINTER",1,0),
"PLUMBER",if(Resources[Category]="PLUMBER",1,0),
"ELECTRICIAN",if(Resources[Category]="ELECTRICIAN",1,0),
"CARPENTER",if(Resources[Category]="CARPENTER",1,0),
"AC",IF(Resources[Category]="AC",1,0))

and i'm able to acheive the result.

My question is , is this the right way to achieve this ? or should i make use of Power query merge (transform tools with pivot)

are there any disadvantages in this way?

anybody kindly guide

Thanks for helping!

[dejadpower]

i'm an absolute beginner in power bi, so i have confusion between choosing dax or power query

[lbendlin]

The preference is to use neither, but use the data model instead.

After that it is a question of mutability. If your results are impacted by filter choices then you need a DAX measure. If not then you can create a DAX calculated column or do it in Power Query. 

[v-yifanw-msft · Accepted solution]

Hi @dejadpower ,
Thanks @lbendlin  for the prompt reply. I have created some data to solve this issue based on the information you provided and you can follow the steps below:
1.In Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvc3MDAwVNJRCkotzi8tSk5VAHGMDIxM9I30jRBMY6VYHWyqTRFKTJCZcNVGyKqRDDRDMM1xqDbEarYFDtUoSrCpNkZWbYzVbEMDhHITHE4xQ2bGxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work order" = _t, Resource = _t, #"Start Time" = _t, #"End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work order", type text}, {"Resource", type text}, {"Start Time", type date}, {"End Time", type date}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Resource Table"}),
    #"Removed Alternate Rows" = Table.AlternateRows(#"Appended Query",7,5,13),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Alternate Rows",{"Resource Category"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Resource"}, #"Resource Table", {"Resource"}, "Resource Table", JoinKind.LeftOuter),
    #"Expanded Resource Table" = Table.ExpandTableColumn(#"Merged Queries", "Resource Table", {"Resource Category"}, {"Resource Table.Resource Category"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Resource Table", List.Distinct(#"Expanded Resource Table"[#"Resource Table.Resource Category"]), "Resource Table.Resource Category", "Resource", List.Count),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Painter", Int64.Type}, {"Mason", Int64.Type}, {"Electrician", Int64.Type}, {"Plumber", Int64.Type}})
in
    #"Changed Type1"

2.Add new Measures.

Start time_ =
VAR _1 = SELECTEDVALUE( 'Bookings Table'[Work order] )
CALCULATE (
    MIN ( 'Bookings Table'[Start Time] ),
    FILTER (
        ALL ( 'Bookings Table' ),
        'Bookings Table'[Work order] = _1
    )
)

End time_ =
VAR _1 = SELECTEDVALUE( 'Bookings Table'[Work order] )
CALCULATE (
    MAX ( 'Bookings Table'[End Time] ),
    FILTER (
        ALL ( 'Bookings Table' ),
        'Bookings Table'[Work order] = _1
    )
)

Days =
DATEDIFF ( [Start time_], [End time_], DAY ) + 1

Fianl output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[dejadpower]

thank you, i TRIED your power query version, it works, one last thing pending is I need help in calculating the date difference duration for each work order. i have already posted a question, can you suggest a workaround in this

Re: finding Date Difference grouped - Microsoft Fabric Community

[dejadpower]

thank you @v-yifanw-msft . I'll try your suggestion and update here. 

As per your suggestion, Power query is more preferrable than DAX ?

[lbendlin]

What is preventing you from wiring these tables up in the data model?