DAX to filter multiple values in one column

[koorosh]

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=11435...

What is an easy solution?

[koorosh · Accepted solution]

As an alternative solution check the following:

https://drive.google.com/file/d/1_MdjBI60hzixXl799L80KeY3XXLEkGNQ/view?usp=sharing

[PaulDBrown]

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

[koorosh]

How we can have company and date columns in Intersect table?

https://drive.google.com/file/d/1ibg2jv4ecPffXCJCqCQz4J6EjLIKxh6t/view?usp=sharing

[PaulDBrown]

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]

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.