cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
orange88 Frequent Visitor
Frequent Visitor

DAX measure to filter for multiple values in one column and one value in a second column

I am having trouble coming up with a measure to count the number of students who have attended a set of subjects in the below data table. For example, if I wanted to count the number of students who have attended all Calculus, History, and Writing subjects, I would want the measure to output only Charles, as  he is the only student has met this criteria. I came up with the following: 

AllTopics = COUNTROWS(FILTER('Table1',('Table1'[Subject] = "Calculus" || 'Table1'[Subject] = "History" || 'Table1'[Subject] = "Writing") && 'Table1'[Present] = "Yes")).
 

Subject

Present

Student

Physics

Yes

Charles

Physics

Yes

Daryl

Physics

Yes

John

Physics

Yes

Jess

Calculus

Yes

Daryl

Calculus

Yes

Charles

Calculus

Yes

Charles

Calculus

Yes

Charles

Calculus

Yes

Henry

Calculus

Yes

Jess

Calculus

Yes

Charles

Calculus

Yes

Charles

History

Yes

Lucas

History

Yes

Charles

History

Yes

Edgar

History

Yes

Charles

History

Yes

Charles

History

Yes

Edgar

History

Yes

Edgar

History

Yes

Charles

History

Yes

Tess

Calculus

No

Charles

History

No

Lucas

History

No

Charles

History

No

Edgar

History

No

Charles

History

No

Charles

History

No

Edgar

History

No

Edgar

History

No

Charles

History

No

Tess

History

No

Edgar

Writing

No

Daryl

Writing

No

Edgar

Writing

No

Tess

Writing

No

Tess

Writing

No

Edgar

Writing

Yes

Tess

Writing

Yes

Charles

Writing

Yes

Edgar

Writing

Yes

Edgar

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: DAX measure to filter for multiple values in one column and one value in a second column

Hi @orange88 

 

Please use the measure below:

Measure 2 = 
VAR PassStudent =
    FILTER (
        SUMMARIZE (
            CALCULATETABLE ( 'Table1', ALL ( 'Table1'[Subject] ) ),
            Table1[Student],
            "AllPass", IF (
                CALCULATE (
                    COUNTROWS ( DISTINCT ( 'Table1' ) ),
                    FILTER (
                        'Table1',
                        'Table1'[Present] = "Yes"
                            && 'Table1'[Subject] IN { "Calculus", "History", "Writing" }
                    )
                ) >= 3,
                1,
                0
            )
        ),
        [AllPass] = 1
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table1' ),
            FILTER (
                'Table1',
                'Table1'[Present] = "Yes"
                    && 'Table1'[Subject] IN { "Calculus", "History", "Writing" }
            )
        ) > 0,
        CALCULATE (
            DISTINCTCOUNT ( 'Table1'[Student] ),
            FILTER (
                'Table1',
                'Table1'[Student] IN SELECTCOLUMNS ( PassStudent, "Student", 'Table1'[Student] ) && 'Table1'[Subject] IN { "Calculus", "History", "Writing" } && 
                'Table1'[Present] = "Yes"
            )
        ),
        BLANK ()
    )

4.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

3 REPLIES 3
Super User I
Super User I

Re: DAX measure to filter for multiple values in one column and one value in a second column

@orange88 

 

Check out this thread and see if it solves your quest for a "measure":

 

How to count values that satisfy multiple conditions in a column 

 

 




If this post has helped you, please give it a thumbs up!
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!
Paul on Linkedin.






Super User IV
Super User IV

Re: DAX measure to filter for multiple values in one column and one value in a second column

Try

CALCULATE(COUNTX(filter(SUMMARIZE(Student,"s1",COUNTROWS(FILTER('Table1',('Table1'[Subject] = "Calculus" || 'Table1'[Subject] = "History" || 'Table1'[Subject] = "Writing") && 'Table1'[Present] = "Yes"))),[s1]>=3),[s1]))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Community Support
Community Support

Re: DAX measure to filter for multiple values in one column and one value in a second column

Hi @orange88 

 

Please use the measure below:

Measure 2 = 
VAR PassStudent =
    FILTER (
        SUMMARIZE (
            CALCULATETABLE ( 'Table1', ALL ( 'Table1'[Subject] ) ),
            Table1[Student],
            "AllPass", IF (
                CALCULATE (
                    COUNTROWS ( DISTINCT ( 'Table1' ) ),
                    FILTER (
                        'Table1',
                        'Table1'[Present] = "Yes"
                            && 'Table1'[Subject] IN { "Calculus", "History", "Writing" }
                    )
                ) >= 3,
                1,
                0
            )
        ),
        [AllPass] = 1
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table1' ),
            FILTER (
                'Table1',
                'Table1'[Present] = "Yes"
                    && 'Table1'[Subject] IN { "Calculus", "History", "Writing" }
            )
        ) > 0,
        CALCULATE (
            DISTINCTCOUNT ( 'Table1'[Student] ),
            FILTER (
                'Table1',
                'Table1'[Student] IN SELECTCOLUMNS ( PassStudent, "Student", 'Table1'[Student] ) && 'Table1'[Subject] IN { "Calculus", "History", "Writing" } && 
                'Table1'[Present] = "Yes"
            )
        ),
        BLANK ()
    )

4.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors