Deduplicate ID based on not superior rank

[Quentin_]

Hello,

I try to find the right method to calculate the number of deduplicated ID with a restriction values in the same column but I block when I need to used multiple filters.

Example : Table_ID_rank  -> 

Description about the values in the table "ID + completion" (this table indicate the completion by ID).

During the ID navigation, this ID can be send multiple values (0%, 50%, 75% and 100% if it's complete). If an ID come back, it will send the last completion (exemple not 0%, not 50%, 75%, 100% if it finish).

Values in the table :

ID	Completion (int)
abcdef	50
abcdef	75
abcdef	100
poujkd	100
qwerty	100
plodse	50
plodse	75
siropderable	50
docsoqqp	50
docsoqqp	75

I would like count the number of ID by rank with a restriction on the top row.

Example

Unique ID for the rank "50" : result -> 1 (ID : siropderable) Unique ID on my table at the range "50" 

Unique ID for the rank "75" : result -> 2 (ID : plodse and docsoqqp)

Unique ID for the rank "100" : result -> 3 (ID : abcdef, poujkd, qwerty) 

For the rank 100 i used the following dax query :

ID completion 100 = CALCULATE(DISTINCTCOUNT('Table_ID_rank[ID]); 'Table_ID_rank[ID] = 100)

Results : 3 - OK

ID	Completion (int)
abcdef	100
poujkd	100
qwerty	100

Problem is for the inferior rank with a restriction.

I tried for the rank 75 the following query :

ID completion 75= CALCULATE(DISTINCTCOUNT('Table_ID_rank[ID]); ('Table_ID_rank[ID] = 75 & 'Table_ID_rank[ID] <> 100))

Results : 3 - NOK

ID	Completion (int)
abcdef	75
plodse	75
docsoqqp	75

I would like obtain 2 because id "abcdef" will be not add to this result (he has a value 100 in the table).

Have you got an idea to fix my problem ?

Thank you,

[Anonymous]

Hi @Quentin_ ,

Create a measure as below:

_Completion (int) = 
var maxvalue=MAXX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])),'Table'[Completion (int)])
Return
IF(maxvalue in FILTERS('Table'[Completion (int)]),maxvalue,BLANK())

Finally you will see:

For the related .pbix file, pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Quentin_ , For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

How to use to find previous value

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[harshnathani · Accepted solution]

Hi @Quentin_ ,

You can approach the solution like this.

Create 2 Calculated Column.

Ranking Column = RANKX(FILTER('Table','Table'[D] = EARLIER('Table'[D])),'Table'[Completion (int)])

Previous Completion = 
CALCULATE(MAX('Table'[Completion (int)]),FILTER('Table','Table'[D] = EARLIER('Table'[D])  && 'Table'[Ranking Column] = EARLIER('Table'[Ranking Column])-1))

Then create 3 measures.

Count100 = CALCULATE(DISTINCTCOUNT('Table'[D]), FILTER('Table','Table'[Completion (int)] = 100 && 'Table'[Previous Completion] = BLANK()))

Count75 = CALCULATE(DISTINCTCOUNT('Table'[D]), FILTER('Table','Table'[Completion (int)] = 75 && 'Table'[Previous Completion] = BLANK())) 

Count50 = CALCULATE(DISTINCTCOUNT('Table'[D]), FILTER('Table','Table'[Completion (int)] = 50 && 'Table'[Previous Completion] = BLANK()))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)