Forum Discussion

yogeshk77's avatar
yogeshk77
Helper I
3 years ago
Solved

Multiple rows into a single column with Many to Many relationship

I have following tables with two level parent child hirarchy

 

 

 

And the output I need is a , comma separated Repo Names column in Story Table as seen below ->

 

Can someone please help me with a DAX query for the same?

  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Repos =
VAR CurrentStory = Story[Story ID]
VAR PRs =
    CALCULATETABLE (
        VALUES ( 'Story PR'[PR ID] ),
        TREATAS ( { CurrentStory }, 'Story PR'[Story ID] )
    )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( 'PR Repo'[Repo name] ),
            TREATAS ( PRs, 'PR Repo'[PR ID] )
        ),
        'PR Repo'[Repo name],
        ", "
    )

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Try

    Repos =
VAR CurrentStory = Story[Story ID]
VAR PRs =
    CALCULATETABLE (
        VALUES ( 'Story PR'[PR ID] ),
        TREATAS ( { CurrentStory }, 'Story PR'[Story ID] )
    )
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( 'PR Repo'[Repo name] ),
            TREATAS ( PRs, 'PR Repo'[PR ID] )
        ),
        'PR Repo'[Repo name],
        ", "
    )