Skip to main content
cancel
Showing results for 
Search instead 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

Reply
hashtag_pete
Helper V
Helper V

count occurences of multiple values

Hello community, 

I struggle with the following request:

I count the occurence of a certain value (here 50) in multiple columns with this query

let
    source = 
    #table(
        {"Width A", "Width B", "Width C"},
        {{50, 50, 50}, {40, 50, 50}, {40, 40, 40}, {45, 40, 40}}
    ),
    #"Changed Type" = Table.TransformColumnTypes(source,{{"Width A", Int64.Type}, {"Width B", Int64.Type}, {"Width C", Int64.Type}}),
    #"Count 50&45" = Table.AddColumn(#"Changed Type", "Count", each 
        List.Count(
            List.Select(
                Record.ToList(
                    Record.SelectFields(_, {"Width A", "Width B", "Width C"})
                ), each _ = 50 
            )
        )
    )
in
    #"Count 50&45"

 this shall now also count the number 45, so that the fourth row in the example returns 1

How can I achieve this? It sounds so simple, but I can't figure it out... 

 

thanks a lot in advance

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Change each _ = 50 to

each _ = 50 or _ = 45

View solution in original post

4 REPLIES 4
hashtag_pete
Helper V
Helper V

OMG... unbelievable... I knew the solution would be simple, but these syntax errors always give me rest...

Thanks so much.

Tip: If you need to check for more than just a couple of values, you can use List.Contains instead of a bunch of comparisons combined with "or".

 

For example, instead of

each _ = 50 or _ = 49 or _ = 48 or _ = 47 or _ = 46 or _ = 45

write the following:

each List.Contains({50,49,48,47,46,45}, _)

Thanks, @AlexisOlson , I was looking for something similar to the IN statement in DAX, but wasn't succesfull. 

This defenitely helps!

Vijay_A_Verma
Super User
Super User

Change each _ = 50 to

each _ = 50 or _ = 45

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors