Forum Discussion

Asking's avatar
Asking
Regular Visitor
3 years ago
Solved

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...
  • tamerj1's avatar
    tamerj1
    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]
    )
tamerj1's avatar
tamerj1
Community Champion
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's avatar
    Asking
    Regular Visitor
    3 years ago

    Thank you. That helped.

    Is there a way I can show the result as a stacked bar chart. I would like the "combination" to be in the Y axis and the "count" in the X but I understand I can't put a measure in the Y...

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Hi Asking 
      I hope this is what you're looking for. Please refer to attached updated sample file.

      Combinations = 
SELECTCOLUMNS (
    SUMMARIZE (
        'Table',
        'Table'[Column1],
        "@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," )
    ),
    "Combination", 
    [@Combination]
)
      Count2 = 
VAR T1 =
    SUMMARIZE (
        'Table',
        'Table'[Column1],
        "@Combination", CONCATENATEX ( 'Table', 'Table'[Column2], "," )
    )
VAR T2 =
    SUMMARIZE ( 
        T1, 
        [@Combination],
        "@Count", COUNTROWS ( FILTER ( T1, [@Combination] = EARLIER ( [@Combination] ) ) )
    )
RETURN
    MAXX ( 
        FILTER ( T2, [@Combination] = SELECTEDVALUE ( Combinations[Combination] ) ),
        [@Count]
    )