Forum Discussion

stvn43's avatar
stvn43
Frequent Visitor
3 years ago

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
Need Help

3 Replies

  • stvn43's avatar
    stvn43
    Frequent Visitor
    3 years ago

    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
  • stvn43's avatar
    stvn43
    Frequent Visitor
    3 years ago

    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!
  • bolfri's avatar
    bolfri
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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/