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

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 Plate Site NP 1 Site 1 NP 2 Site 2 NP 3 Site 3 NP 4 Site 1 NP 1 Site 2 NP 2 Site 1 NP 2 Site 3 NP 1 Site 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 Plate Site 1 Site 2 Site 3 NP 1 2 1 NP 2 1 1 1 NP 3 1 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

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

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

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

Frequent Visitor

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

Super User

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

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

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