Forum Discussion

koorosh's avatar
koorosh
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

DAX to filter multiple values in one column

Hello Experts,
Why the following dax does not filter multiple choice in one column like "AND" operator in "IF" function?

 

Table = SUMMARIZECOLUMNS('Sheet1'[User],'Sheet1'[company],'Sheet1'[Certification],
filter('Sheet1',[Certification] in {"Cert A"} && [Certification] in {"Cert B"})
)

 

https://docs.google.com/spreadsheets/d/1Z30vfCPh6RN_Vxx7f2qtCgJQw2xjqSqM/edit?usp=sharing&ouid=114355704148225428863&rtpof=true&sd=true

 

What is an easy solution?

 

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    Try:

    Table = SUMMARIZE(filter('Sheet1', Sheet1[Certification] in {"Cert A"} && Sheet1[Certification] in {"Cert B"}), 'Sheet1'[User],'Sheet1'[company],'Sheet1'[Certification])
    )

     

    Though I'm not sure if that is what you are after. The && in the filter means the values should be Cert A and Cert B, as opposed to Cert A or Cert B

    If it's OR you are after, try:

    Table = SUMMARIZE(filter('Sheet1', Sheet1[Certification] in {"Cert A", "Cert B"}), 'Sheet1'[User],'Sheet1'[company],'Sheet1'[Certification])
    )

     

    • koorosh's avatar
      koorosh
      Icon for Post Partisan rankPost Partisan

      I got nothing.

      As you guessed I am looking for AND combination. User Name1 has Cert A and Cert B in the certification column. So I am looking for the DAX function that returns User Name 1 in its result.

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    Try:

    A & B =

    VAR A = CALCULATATBLE(VALUES(Sheet1[User]), Sheet1[Certification] = "Cert A")

    VAR B = CALCULATATBLE(VALUES(Sheet1[User]), Sheet1[Certification] = "Cert B") 

    RETURN

    COUNTROWS(INTERSECT(A , B))

    use the measure in a visual as a filter in the filter pane an set the value to 1