Forum Discussion
Asking
3 years agoRegular Visitor
Comma Separated List
Hi, I have 1 table with the first column as ID: 1 X Y 1 X1 Y 2 X Z 2 X1 Z 3 X2 Z 1 X4 F After this table is filterd by a slicer on the report, Let's say Y and Z from 3rd column are chos...
- 3 years ago
Hi Asking
This not as simple as it sounds. The reason is that there is no column to slice by. Long story short, please refer to attched file and following screenshots. You have to have either index or Date column. If don't, then use power query to add an index column. Please let me know if you need any further help.Combination = VAR T1 = SUMMARIZE ( 'Table', 'Table'[Column1], "@Index", MAX ( 'Table'[Index] ), "@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," ) ) VAR T2 = SUMMARIZE ( T1, [@Combination], "@@Index", MAX ( 'Table'[Index] ) ) VAR T3 = ADDCOLUMNS ( T2, "@Rank", RANKX ( T2, [@@Index],, ASC, Dense ) ) RETURN MAXX ( FILTER ( T3, [@Rank] = SELECTEDVALUE ( 'Table 2'[Value] ) ), [@Combination] )Count = VAR T1 = SUMMARIZE ( 'Table', 'Table'[Column1], "@Index", MAX ( 'Table'[Index] ), "@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," ) ) VAR T2 = SUMMARIZE ( T1, [@Combination], "@@Index", MAX ( 'Table'[Index] ), "@Count", COUNTROWS ( FILTER ( T1, [@Combination] = EARLIER ( [@Combination] ) ) ) ) VAR T3 = ADDCOLUMNS ( T2, "@Rank", RANKX ( T2, [@@Index],, ASC, Dense ) ) RETURN MAXX ( FILTER ( T3, [@Rank] = SELECTEDVALUE ( 'Table 2'[Value] ) ), [@Count] )
Asking
3 years agoRegular Visitor
Thank you.
However, how do I create the actual table and not only get the scalar value please?