Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
This is my table :
I need to calculate the number of ids declined by value_bet, keeping only the value corresponding to the most recent row (i.e. the min of rank_bet for each employee).
I've tried using this expression
CALCULATE(DISTINCTCOUNT(bet[id]), bet[rank_bet]= MINX(FILTER(bet, bet[id] = LASTNONBLANK(bet[id], bet[id])), bet[rank_bet]))
but the calculation of Min(rank_bet) is wrong when I break it down by value_bet: I get the impression that my measure takes the Min(rank_bet) by value_bet group and not grouped by id.
Example of expected result :
Solved! Go to Solution.
I'm not sure why this filter is needed for a distinct count of id calculation but I'd approach it like this:
VAR _MinRanks_ =
SUMMARIZE (
bet,
bet[id],
"MinRank", MIN ( bet[rank_bet] )
)
RETURN
CALCULATE (
DISTINCTCOUNT ( bet[id] ),
TREATAS (
_MinRanks_,
bet[id],
bet[rank_bet]
)
)
I'm not sure why this filter is needed for a distinct count of id calculation but I'd approach it like this:
VAR _MinRanks_ =
SUMMARIZE (
bet,
bet[id],
"MinRank", MIN ( bet[rank_bet] )
)
RETURN
CALCULATE (
DISTINCTCOUNT ( bet[id] ),
TREATAS (
_MinRanks_,
bet[id],
bet[rank_bet]
)
)
thank you, it works perfectly!
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |