Power BI DAX: Data Values Ranking within Subgroup with RANKX

Опубликовано: 27 Декабрь 2024
на канале: Mike YU (BI&Analytics)

2,262

This video demonstrates the ways on how to Rank Values within subgroup in power BI Data Table and Visualization Table Charts by DAX function RANKX with other support functions, such as FILTER, EARLIER and ALL.

For more info of my BI & Analytics, please refer to the following website links:

My BI & Analytics Website:
https://sites.google.com/view/mikegua...

My blog website :
https://mikeyu.lovestoblog.com/

DAX Expressions from the video:

1. BPM Ranking within PN (column) =
VAR _table=
FILTER(
'Blood Pressure list',
'Blood Pressure list'[Measured Personal Name]=EARLIER('Blood Pressure list'[Measured Personal Name])
)
RETURN
RANKX(_table,[Heartbeat],,,Dense)

2. BPM Ranking within PN & POD (column) =
VAR _table=
FILTER(
'Blood Pressure list',
'Blood Pressure list'[Measured Personal Name]=EARLIER('Blood Pressure list'[Measured Personal Name]) &&
'Blood Pressure list'[Parts of Day]=EARLIER('Blood Pressure list'[Parts of Day])
)
RETURN
RANKX(_table,[Heartbeat],,,Dense)

3. BPM Ranking within PN&POD&Years (column) =
VAR _table =
FILTER(
'Blood Pressure list',
'Blood Pressure list'[Measured Personal Name]=EARLIER('Blood Pressure list'[Measured Personal Name]) &&
'Blood Pressure list'[Parts of Day]=EARLIER('Blood Pressure list'[Parts of Day]) &&
'Blood Pressure list'[Local year]=EARLIER('Blood Pressure list'[Local year])
)
RETURN
RANKX(_table,[Heartbeat],,,Dense)

4. BPM_Average (measure) = AVERAGE('Blood Pressure list'[Heartbeat])

5. BPM_Average Rank within date (measure) =
VAR _table=ALL('Blood Pressure list'[Date only])
RETURN
RANKX(_table,[BPM_Average (measure)],,,Dense)

6. BPM_Average Rank within POD (measure) =
VAR _table=ALL('Blood Pressure list'[Parts of Day])
RETURN
RANKX(_table,[BPM_Average (measure)],,,Dense)

7. BPM_Average Rank within POD&Years (measure) =
VAR _table =
ALL('Blood Pressure list'[Parts of Day],'Blood Pressure list'[Local year])
RETURN
RANKX(_table,[BPM_Average (measure)],,,Dense)

References :
RANKX function (DAX) - DAX | Microsoft Learn:
https://learn.microsoft.com/en-us/dax...

FILTER function (DAX) - DAX | Microsoft Learn:
https://learn.microsoft.com/en-us/dax...

EARLIER function (DAX) - DAX | Microsoft Learn:
https://learn.microsoft.com/en-us/dax...

ALL function (DAX) - DAX | Microsoft Learn:
https://learn.microsoft.com/en-us/dax...