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 fo...
Need Help
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!