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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DimaMD
Solution Sage
Solution Sage

Count unique rows plan

Hello community!

I have a task, need your help to solve it. 

 

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 in advance!


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

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

Hi,

Please check the below picture and the measure.

 

Picture2.png

 

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.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
PiEye
Resolver II
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

IF(SELECTEDVALUE(Table[Contract]IN {"Production","Trading"}
,
  DISTINCTCOUNT(Table[product ID]),
  count(Table[product ID]))
 
Does this work for you?
 
Pi
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the measure.

 

Picture2.png

 

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.


Go to My LinkedIn Page


Ні, @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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors