Forum Discussion
Solved
Generating row value combinations
Hi, I am trying to generate combination of values found in row data. Source Data Customer Column2 Paul A Paul B Paul C Tom D Tom E Tom F Jerry H Jerry I ...
This seems close, albeit with duplicates:
Table = VAR __tmpTable1 = SELECTCOLUMNS(ALL('Data'),"__Customer",[Customer],"__Column2",[Column2]) VAR __tmpTable2 = SELECTCOLUMNS(ALL('Data'),"___Customer",[Customer],"___Column2",[Column2]) VAR __tmpTable3 = FILTER(GENERATE(__tmpTable1,__tmpTable2),[__Customer]=[___Customer]&&([__Column2]>[___Column2]||[__Column2]<[___Column2])) VAR __tmpTable4 = ADDCOLUMNS(__tmpTable3,"__CombinedColumn",[__Column2]&"-"&[___Column2]) RETURN SELECTCOLUMNS(__tmpTable4,"Customer",[__Customer],"Combined Column",[__CombinedColumn])Thanks to your solution I made a minor adjustment to step3. Removed "[__Column2]>[___Column2]||". And that gave the exact result I nmeeded.
VAR __tmpTable3 =
FILTER (
GENERATE ( __tmpTable1, __tmpTable2 ),
[__Customer] = [___Customer]
&& ([__Column2] < [___Column2] )
)
This seems close, albeit with duplicates:
Table =
VAR __tmpTable1 = SELECTCOLUMNS(ALL('Data'),"__Customer",[Customer],"__Column2",[Column2])
VAR __tmpTable2 = SELECTCOLUMNS(ALL('Data'),"___Customer",[Customer],"___Column2",[Column2])
VAR __tmpTable3 = FILTER(GENERATE(__tmpTable1,__tmpTable2),[__Customer]=[___Customer]&&([__Column2]>[___Column2]||[__Column2]<[___Column2]))
VAR __tmpTable4 = ADDCOLUMNS(__tmpTable3,"__CombinedColumn",[__Column2]&"-"&[___Column2])
RETURN SELECTCOLUMNS(__tmpTable4,"Customer",[__Customer],"Combined Column",[__CombinedColumn])That's works! Excellent solution!
Thanks a lot for the prompt reply.
I can sort out the duplicate combination.
Thanks you.