Combine 2 Data sets using measure

[hemann]

Hi, I am trying to create the below visual. I have two pieces of data that I need to combine to get the correct results (Data 1 & Data 2). I am ideally looking to create a measure to achieve this. I have created a third piece of data, which is the result needed. Can somebody please help with a neat measure!!

Visual Required;

Data 1

Product	Volume
1	1
2	1
3	1
4	1
5	1
6	1

Data 2 

Product	Feature	Feature Quality	Volume
1	A	High	1
1	B	Low	1
1	C	Low	1
2	A	High	1
2	B	High	1
3	A	Low	1
3	B	High	1
3	C	Low	1
5	A	Low	1
5	C	High	1

Data result (from Data 1 and Data 2)

Feature	Product	Result	Volume
A	1	High	1
B	1	Low	1
C	1	Low	1
A	2	High	1
B	2	High	1
C	2	Not Featured	1
A	3	Low	1
B	3	High	1
C	3	Low	1
A	4	Not Featured	1
B	4	Not Featured	1
C	4	Not Featured	1
A	5	Low	1
B	5	Not Featured	1
C	5	High	1
A	6	Not Featured	1
B	6	Not Featured	1
C	6	Not Featured	1

[hemann]

Thankyou you are a Rockstar. I used the DAX query solution as it processed a little faster...

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@hemann 

you can try to do that in PQ

let
    Source = Table.FromColumns( {List.TransformMany(List.Distinct(#"Data 1"[Product]),(a)=>List.Distinct(#"Data 2"[Feature]),(x,y)=>y),List.TransformMany(List.Distinct(#"Data 1"[Product]),(a)=>List.Distinct(#"Data 2"[Feature]),(x,y)=>x)}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Column2"}, #"Data 1", {"Product"}, "Data 1", JoinKind.LeftOuter),
    #"Expanded Data 1" = Table.ExpandTableColumn(#"Merged Queries", "Data 1", {"Volume"}, {"Data 1.Volume"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Data 1", {"Column1", "Column2"}, #"Data 2", {"Feature", "Product"}, "Data 2", JoinKind.LeftOuter),
    #"Expanded Data 2" = Table.ExpandTableColumn(#"Merged Queries1", "Data 2", {"Feature Quality"}, {"Data 2.Feature Quality"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Data 2",null,"Not Featured",Replacer.ReplaceValue,{"Data 2.Feature Quality"})
in
    #"Replaced Value"

or you can use DAX to create a table

Table = 
VAR tbl=DISTINCT('Data 1'[Product])
VAR tbl2=DISTINCT('Data 2'[Feature])
VAR tbl3=CROSSJOIN(tbl,tbl2)
VAR tbl4=ADDCOLUMNS(tbl3,"F qulaity",if(maxx(FILTER('Data 2',EARLIER([Feature])='Data 2'[Feature]&&EARLIER([Product])='Data 2'[Product]),'Data 2'[Feature Quality])="","Not Featured",maxx(FILTER('Data 2',EARLIER([Feature])='Data 2'[Feature]&&EARLIER([Product])='Data 2'[Product]),'Data 2'[Feature Quality])),"volumn",maxx(FILTER('Data 1',EARLIER([Product])='Data 1'[Product]),'Data 1'[Volume]))
return tbl4

pls see the attachment below