Forum Discussion

IoannisT's avatar
IoannisT
Advocate I
2 years ago
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 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])

 

Need Help
  • IoannisT's avatar
    IoannisT
    2 years ago

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


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

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      IoannisT
      Advocate I
      2 years ago

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


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