Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Socrates
Frequent Visitor

Count Rows by multiple conditions

Hello

 

I'm having the below problem I just can't seem to get my head around. I know the solution is simple but I'm having a complete mental block as to what it could be.

 

Data is set out like:

 

Number PlateSite
NP 1Site 1
NP 2Site 2
NP 3Site 3
NP 4Site 1
NP 1Site 2
NP 2Site 1
NP 2Site 3
NP 1Site 1
NP 3

Site 1

NP 4

Site 1

 

I'd like to count the number plates that have parked at more that one site (could be two, could be all three), ignoring those that have only parked at one (even multiple times). So I'd have an output matrix (or any other visual) like this:

 

Number PlateSite 1Site 2Site 3
NP 121 
NP 2111
NP 31 1

 

Thank you

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have used the following logic:

more than 2 sites = 
VAR __validation = IF( CALCULATE( DISTINCTCOUNT('Table'[Site]); ALL( 'Table'[Site])) > 1; TRUE(); FALSE())

RETURN
    IF(
        __validation;
        COUNTROWS( 'Table');
        BLANK()
    )

Which returns the following matrix

test.PNG

If this also works for you then please mark it as the solution. Kudos is also appreciated Smiley Happy

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

I have used the following logic:

more than 2 sites = 
VAR __validation = IF( CALCULATE( DISTINCTCOUNT('Table'[Site]); ALL( 'Table'[Site])) > 1; TRUE(); FALSE())

RETURN
    IF(
        __validation;
        COUNTROWS( 'Table');
        BLANK()
    )

Which returns the following matrix

test.PNG

If this also works for you then please mark it as the solution. Kudos is also appreciated Smiley Happy

Yes! That's it, thank you. I was doing similar but doing the count on the wrong column. 

AlB
Super User
Super User

Hi @Socrates 

You can just place Number Plate and Site in the rows and columns of a matrix visual and then place Site in values, selecting Count (i.e., implicit measure) or create an explicit measure and place it in values:

Measure = COUNT(Table1[Site])  

 

popov
Resolver III
Resolver III

Hello, @Socrates 

Create measure like this:

Count = COUNTROWS(<YourTableName>)
And add this measure in matrix, in rows place column Number Plate, in columns place column Site

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.