Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
stvn43
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.
 
stvn43_0-1671822778673.png
 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
bolfri
Super User
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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




stvn43
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!
stvn43
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.