Normalized values. Distinct count doesn't work as expected

[IoannisT]

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 measures to calculate the normalization, the values appear correct, the moment I select a userID filter, everything breaks. I believe that I am missing an "allexpept" command. 

Can you please help out?

userID	DeliveryID
wyn	hoc
tup	rya
jud	cwm
gad	bar
sal	alt
ted	dal
tex	pam
zel	tex
ped	abb
gid	vis
wyn	ket
tup	ley
jud	mim
gad	lea
wyn	dit
tup	tot
jud	ted
gad	tup
wyn	mow
tup	yew
jud	rep

min = 
MINX(
    DISTINCT('distinct'[userID]),
    CALCULATE(DISTINCTCOUNT('distinct'[DeliveryID]))
)

max = 
MAXX(
    DISTINCT('distinct'[userID]),
    CALCULATE(DISTINCTCOUNT('distinct'[DeliveryID]))
)

normDelivery =
DIVIDE( (DeliveryID_count-dst) - ([min] ) , ([max]-[min]))

DeriveryID_count-dst = COUNTAX('distinct',[DeliveryID])

[Anonymous]

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

[IoannisT · Accepted solution]

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]))


------------------------------------------------------------------