RANX gives duplicates and skips numbers when filter is applied

gspollock — Fri, 13 Sep 2024 20:14:09 GMT

I am requesting assistance on a report that is supposed to rank the Sales data. There is a slicer on the SECONDARY column. When SECONDARY = N, the data has only one row for each CUST NUM/CUSTOMER combination. When SECONDARY = Y is included in the data, there are multiple rows for each CUST NUM/CUSTOMER combination.

The RANKX seems to work correctly when SECONDARY = N. When either Y or both Y and N are selected in the slicer, it is giving duplicates for some rows that aren't identical and skipping some ranks.

This is the function: Rank Current = RANKX( ALLSELECTED(DIM_CUSTOMER[CUST NUM], DIM_CUSTOMER[CUSTOMER]), FACT_SALES[SALES],,DESC,Dense)

Here is a portion of the underlying data.

CUST NUM	CUSTOMER	ACCOUNT MANAGER	SECONDARY	SALES
390429	Customer11	Account Manager10	Y	774933
390429	Customer11	Account Manager11	Y	774933
390429	Customer11	Account Manager4	Y	774933
575835	Customer21	Account Manager4	Y	671194
575835	Customer21	Account Manager3	Y	671194
575835	Customer21	Account Manager11	Y	671194
575835	Customer21	Account Manager6	Y	671194
575850	Customer18	Account Manager11	Y	877050
575850	Customer18	Account Manager6	Y	873142
575850	Customer18	Account Manager3	Y	877050
575850	Customer18	Account Manager4	Y	873142
733216	Customer3	Account Manager11	Y	420072
733216	Customer3	Account Manager5	Y	420072
733216	Customer3	Account Manager4	Y	420072
2158124	Customer19	Account Manager10	Y	308942
2158124	Customer19	Account Manager11	Y	308942
2158124	Customer19	Account Manager4	Y	308942
2491218	Customer4	Account Manager4	Y	450020
2491218	Customer4	Account Manager3	Y	900040
2491218	Customer4	Account Manager11	Y	450020
3751962	Customer17	Account Manager5	Y	2742127
3751962	Customer17	Account Manager4	Y	2742127
3751962	Customer17	Account Manager11	Y	2742127
3832376	Customer7	Account Manager2	Y	480377
3832376	Customer7	Account Manager10	Y	480377
3832376	Customer7	Account Manager4	Y	480377
3832376	Customer7	Account Manager16	N	480377
3832376	Customer7	Account Manager11	Y	480377
3832376	Customer7	Account Manager13	Y	480377

Thanks for your assistance.

Re: RANX gives duplicates and skips numbers when filter is applied

lbendlin — Fri, 13 Sep 2024 23:56:55 GMT

Please follow this article

Use of RANKX in Power BI measures - SQLBI

Re: RANX gives duplicates and skips numbers when filter is applied

Anonymous — Mon, 23 Sep 2024 08:52:37 GMT

Hi lbendlin ,thanks for the quick reply, I'll add more.

Hi gspollock ,

Try this

Measure = SUM('Table'[SALES])

Measure2 = RANKX(ALLSELECTED('Table'[CUST NUM],'Table'[CUSTOMER],'Table'[ACCOUNT MANAGER],'Table'[SECONDARY]),[Measure],,DESC,Dense)

Final output

Best Regards,
Wenbin Zhou

topic Re: RANX gives duplicates and skips numbers when filter is applied in DAX Commands and Tips

RANX gives duplicates and skips numbers when filter is applied

Re: RANX gives duplicates and skips numbers when filter is applied

Re: RANX gives duplicates and skips numbers when filter is applied