cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Solution Sage

## Count unique rows plan

Hello community!

We have two tables, one of them - date and month; second - customer and product ID's, number of line.

Based on one month (february) our goal is to count how many rows do we have in this month but with specific conditions:

1) If data in collum "contract" = "own production", "trading", then we count all those rows seperately as unique ones

2) If data in collum "contract" <> "own production", "trading", then we consider only unique data. For example we have 3 rows with same number 1817, in this case we count only as 1 unique row

In example (february) we have 22 rows. So the goal in our example, considering conditios I mentioned abowe, should be 19 unique rows

Example file pbix

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
1 ACCEPTED SOLUTION
Super User

Hi,

Please check the below picture and the measure.

``````fix measure =
VAR conditiontable =
FILTER ( Plan, Plan[Contract] IN { "Own production", "Trading" } )
VAR nonconditiontable =
SUMMARIZE ( EXCEPT ( Plan, conditiontable ), Plan[Contract] )
RETURN
COUNTROWS ( conditiontable ) + COUNTROWS ( nonconditiontable )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

3 REPLIES 3
Resolver II

In this example, you can use an IF() statement to check whether the column has the value you wish, and then returns a different count depending on it.

"IN()" checks the value against a list of values.

EG

,
DISTINCTCOUNT(Table[product ID]),
count(Table[product ID]))

Does this work for you?

Pi
Super User

Hi,

Please check the below picture and the measure.

``````fix measure =
VAR conditiontable =
FILTER ( Plan, Plan[Contract] IN { "Own production", "Trading" } )
VAR nonconditiontable =
SUMMARIZE ( EXCEPT ( Plan, conditiontable ), Plan[Contract] )
RETURN
COUNTROWS ( conditiontable ) + COUNTROWS ( nonconditiontable )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Solution Sage

Ні, @Jihwan_Kim
Thanks for the help, your measure worked

Greetings from Ukraine.

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors