topic Count unique rows plan in DAX Commands and Tips

Count unique rows plan

DimaMD — Tue, 29 Mar 2022 07:10:23 GMT

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!

Re: Count unique rows plan

Jihwan_Kim — Tue, 29 Mar 2022 07:36:47 GMT

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 )

Re: Count unique rows plan

PiEye — Tue, 29 Mar 2022 07:39:16 GMT

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.

IF(SELECTEDVALUE(Table[Contract]) IN {"Production","Trading"}
,

DISTINCTCOUNT(Table[product ID]),

count(Table[product ID]))

Does this work for you?

Re: Count unique rows plan

DimaMD — Tue, 29 Mar 2022 08:04:07 GMT

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

Greetings from Ukraine.