Forum Discussion

Kyle_Willey's avatar
Kyle_Willey
Frequent Visitor
2 years ago
Solved

Summarize 3 Columns into 1

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.
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    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]
)

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    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's avatar
      Kyle_Willey
      Frequent Visitor
      2 years ago

      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]
      )