Ranking Without any aggregate expression

Hi Power BI Gurus,

I have below set of data

Customer Id	Calendar Date	Sale Id	Standing Order Id
123	14-NOV-13 00:00:00	15264717659	11674594922
123	05-DEC-13 00:00:00	15285099070	11674594922
123	16-DEC-13 00:00:00	15292036294	11674594922
123	03-JAN-14 00:00:00	15301803017	15474692288
123	17-JAN-14 00:00:00	15312660566	15474692288
123	31-JAN-14 00:00:00	15324696479	15474692288
123	14-FEB-14 00:00:00	15337592777	15474692288
123	28-FEB-14 00:00:00	15349170494	15474692288
123	14-MAR-14 00:00:00	15361461738	15474692288

Now I want an additionla column say SO Instance with value like below, its almost ranking Customer on the base of its SO ID ,Sale ID and Calendar Date

Customer Id	Calendar Date	Sale Id	Standing Order Id	SO Instance
123	14-NOV-13 00:00:00	15264717659	11674594922	1
123	05-DEC-13 00:00:00	15285099070	11674594922	2
123	16-DEC-13 00:00:00	15292036294	11674594922	3
123	03-JAN-14 00:00:00	15301803017	15474692288	1
123	17-JAN-14 00:00:00	15312660566	15474692288	2
123	31-JAN-14 00:00:00	15324696479	15474692288	3
123	14-FEB-14 00:00:00	15337592777	15474692288	4
123	28-FEB-14 00:00:00	15349170494	15474692288	5
123	14-MAR-14 00:00:00	15361461738	15474692288	6

The data has mumtiple customer IDs with respective Sae and SO IDs.

Appreciate yor help in this.

Thanks

Swapnil

Need Help

Zubair_Muhammad

8 years ago

Hi Anonymous

Try this calculated column

What is the role of Sale ID in RANKING?

SO_Instance =
RANKX (
    FILTER (
        ALL ( TableName ),
        TableName[Customer Id] = EARLIER ( TableName[Customer Id] )
            && TableName[Standing Order Id] = EARLIER ( TableName[Standing Order Id] )
    ),
    TableName[Calendar Date],
    ,
    Asc,
    DENSE
)

Forum Discussion

Ranking Without any aggregate expression

5 Replies

Related content

Duplicate ranking

Ranking KPI card

Rank with conditions

Dynamic Ranking

DAX expression aggregation