Summarize 3 Columns into 1

[Kyle_Willey]

Hi guys. I have something that I thought should have been easy, but I am not able to find a way to do this. I have a table with results in 3 different columns. I am trying to get a bar chart that will give me a count of each result if it is found in any of the 3 columns. I can get it if I stack the bars, but I want a single count, not one on top of the other. I tried to Summarize, but I still get 3 columns. Is there a way to do the below, but stack all three columns into one result column?

Current DAX:

CLABSIPathogens =

SUMMARIZE(

    'CLABSI Line List',

    'CLABSI Line List'[eventDate],

    'CLABSI Line List'[Pathogen1Name],'CLABSI Line List'[Pathogen2Name],'CLABSI Line List'[Pathogen3Name]

)

This gives me 4 columns as you can see. I want only eventDate and a single result column.

Thanks in advance.

[Fowmy · Accepted solution]

@Kyle_Willey 

I am not sure if I understood your question but if you you need to pull out two columns from the SUMMARIZE table, do the following, you can repeat and apply for other combinations if you need.

CLABSIPathogens =
SELECTCOLUMNS (
    SUMMARIZE (
        'CLABSI Line List',
        'CLABSI Line List'[eventDate],
        'CLABSI Line List'[Pathogen1Name],
        'CLABSI Line List'[Pathogen2Name],
        'CLABSI Line List'[Pathogen3Name]
    ),
    'CLABSI Line List',
    'CLABSI Line List'[Pathogen1Name]
)

[Kyle_Willey]

Thank you. This gave me what I needed:

CLABSIPathogens =

SELECTCOLUMNS(

    SUMMARIZE(

        'CLABSI Line List',

        'CLABSI Line List'[eventDate],

        'CLABSI Line List'[Pathogen1Name],

        'CLABSI Line List'[Pathogen2Name],

        'CLABSI Line List'[Pathogen3Name]

    ),

    "Pathogen",'CLABSI Line List'[Pathogen1Name],

    "Event Date",'CLABSI Line List'[eventDate]

)