cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Partisan

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"})
)

What is an easy solution?

1 ACCEPTED SOLUTION
Post Partisan

As an alternative solution check the following:

5 REPLIES 5
Post Partisan

As an alternative solution check the following:

Community 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

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Post Partisan

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

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

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Post 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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors