Forum Discussion
Distinct count of values when there are corresponding multiple lines per value
How about a calculated column like this?
OrderStatus = VAR AllStatus = CALCULATETABLE ( VALUES ( Orders[On Time or Late] ), ALLEXCEPT ( Orders, Orders[Order] ) ) RETURN IF ( "Late" IN AllStatus, "Late", "On Time" )Once you have this, it should be straightforward to write measures to count distinct orders. E.g.
LateOrderCount = CALCULATE ( DISTINCTCOUNT ( Orders[Order] ), Orders[OrderStatus] = "Late" )Thanks AlexisOlson ! Your solution worked just fine....it was my error. In my OrderCount measures I had accidentally picked "orderstatus" instead of just "order" for the first expression. Corrected measure is below.
OnTimeOrderCount =CALCULATE ( DISTINCTCOUNT ( ShippedOrdersALL_Query[Order] ), ShippedOrdersALL_Query[OrderStatus] = "On Time" )
Super UserHow about a calculated column like this?
OrderStatus =
VAR AllStatus =
CALCULATETABLE (
VALUES ( Orders[On Time or Late] ),
ALLEXCEPT ( Orders, Orders[Order] )
)
RETURN
IF ( "Late" IN AllStatus, "Late", "On Time" )
Once you have this, it should be straightforward to write measures to count distinct orders. E.g.
LateOrderCount =
CALCULATE ( DISTINCTCOUNT ( Orders[Order] ), Orders[OrderStatus] = "Late" )AlexisOlson This looks promising but I am getting a count of "1" for Late. Seems it is counting the distinct value "Late" as just one value. Rather than the distinct count of Orders that are late.
- AlexisOlson
That's because you have Line in your table visual creating a filter context. You'll need to add something like ALLEXCEPT ( Orders, Orders[Order] ) to the CALCULATE in the measure if you want to remove that filter context.
AlexisOlson Yes, understood. Your measure works, but for the LastOrderCount, if put on a card visual, it just returns the number "1". This is what I was referring to. I was expecting the number "50"