Forum Discussion
Solved
Normalized values. Distinct count doesn't work as expected
Hi Fabric community I have the following userID, DeliveryID table (all text) where I want to countdistinct and normalise the values between 0 and 1 for a graph. When I create the min/max meas...
Hi @ v-yaningy-msft
Thanks for reachig out.
I have found the solution on nother forum. Posting here the code for anyone how might find it useful in the future.xxxaaammmm_MIN = CALCULATE( MINX( DISTINCT('distinct'[userID]), CALCULATE(DISTINCTCOUNT('distinct'[DeliveryID])) ), ALL('distinct'[userID])) ------------------------------------------------------------------ xxxaaammmm_MAX = CALCULATE( MAXX( DISTINCT('distinct'[userID]), CALCULATE(DISTINCTCOUNT('distinct'[DeliveryID])) ), ALL('distinct'[userID])) ------------------------------------------------------------------ DeriveryID_count-dst = COUNTAX('distinct',[DeliveryID]) ------------------------------------------------------------------ Normalized measure = DIVIDE( ('distinct'[DeriveryID_count-dst] - 0), ('distinct'[xxxaaammmm_MAX]-0)) ------------------------------------------------------------------ Normalized measure = DIVIDE( ('distinct'[DeriveryID_count-dst] - 'distinct'[xxxaaammmm_MIN]), ('distinct'[xxxaaammmm_MAX]-'distinct'[xxxaaammmm_MIN])) ------------------------------------------------------------------
Hi, IoannisT
It's hard to understanding what went wrong in the screenshot you posted, what was the final result you were trying to achieve? You can briefly describe the logic of your realization to help solve the problem faster.
Best Regards,
Yang
Community Support Team
Hi @ v-yaningy-msft
Thanks for reachig out.
I have found the solution on nother forum. Posting here the code for anyone how might find it useful in the future.
xxxaaammmm_MIN = CALCULATE(
MINX(
DISTINCT('distinct'[userID]),
CALCULATE(DISTINCTCOUNT('distinct'[DeliveryID]))
),
ALL('distinct'[userID]))
------------------------------------------------------------------
xxxaaammmm_MAX = CALCULATE(
MAXX(
DISTINCT('distinct'[userID]),
CALCULATE(DISTINCTCOUNT('distinct'[DeliveryID]))
),
ALL('distinct'[userID]))
------------------------------------------------------------------
DeriveryID_count-dst = COUNTAX('distinct',[DeliveryID])
------------------------------------------------------------------
Normalized measure =
DIVIDE(
('distinct'[DeriveryID_count-dst] - 0), ('distinct'[xxxaaammmm_MAX]-0))
------------------------------------------------------------------
Normalized measure =
DIVIDE(
('distinct'[DeriveryID_count-dst] - 'distinct'[xxxaaammmm_MIN]), ('distinct'[xxxaaammmm_MAX]-'distinct'[xxxaaammmm_MIN]))
------------------------------------------------------------------