cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Count Unique Order by DistinctCount of Multiple Columns

I have a data set (one table) with lots of repeating values, but I need to define order types based on this data. To classify the order types, I need to get the distinct counts from three columns for each order. The order types will be Simple and Complex. So, I want to count the number of orders that have a distinct count of 1 from columns [destid] and [groupid]. Complex orders will have many groups and destinatons.

Since I want to do this for each order, my thinking was to count the number of unique orders with the additional criteria of having < 2 distinct for each of the other columns.
I'm not understanding how to embed the other distinct counts to qualify the orders. Any help is appreciated.

This measure doesn't work, but it's what I have so far:

Simple Order Types =
VAR SimpleOrderCount =
CALCULATE(
DISTINCTCOUNT(orderTypeData[orderid])
(DISTINCTCOUNT(orderTypeData[groupid] < 2 && DISTINCTCOUNT(orderTypeData[destid] < 2 && DISTINCTCOUNT(orderTypeData[adid] < 2)))
Return
SimpleOrderCount
3 REPLIES 3
Super User

Hi,

can you provide some sample eg for 2-3 simple and 2-3 complex orders? And expected results for each of them to understand the problem? Example ordernumber 62392 should be simple, order 123456 should be complex.

You can attach data using https://wetransfer.com/

Proud to be a Super User!

Frequent Visitor

This is what I landed on:

VAR SimpleOrdersDest =
COUNTROWS(
FILTER(
DISTINCT(orderTypeData[ordernumber]),
CALCULATE(DISTINCTCOUNT(orderTypeData[destid]) <= 2 && CALCULATE(DISTINCTCOUNT(orderTypeData[groupid]) <= 2) && CALCULATE(DISTINCTCOUNT(orderTypeData[adid]) <= 2))
)
)

Return
SimpleOrdersDest

It allows me to filter against the DistinctCount value in the query so I can now tag the "simple" order configurations that are candidates for automation and modify the "simple" definition for slicing the environment data differently -- thanks for the sample!
Frequent Visitor

I modified the measure. Still not working:

Simple Order Types =
VAR SimpleOrderCount =
CALCULATE(
DISTINCTCOUNT(orderTypeData[orderid]),
FILTER(
DISTINCTCOUNT(orderTypeData[groupid] < 2 && DISTINCTCOUNT(orderTypeData[destid] < 2 && DISTINCTCOUNT(orderTypeData[adid] < 2)
))

Return
SimpleOrderCount