Count Unique Order by DistinctCount of Multiple Columns

[stvn43]

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

[bolfri]

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/

[stvn43]

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!

[stvn43]

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