[Help] Add a Rank column correctly in a Chart of type Table based on the summary metrics

[Hugh-Guan]

Basic report style

I have so far implemented this requirement, but the measurement of 'Rank' in 'Subject Compliance' is incorrect. I achieved the desired effect by limiting the value of the chart filter 'ECG DataUpload'.

Report content

1. The chart type for 'Subject Compliance' is Table. There are three slicers acting as filters, Protocol ID, Site, and Period.Slicer Protocol ID and Period can be selected only in one option. Site supports multiple options and is selected all by default.
2. The first column 'Subject' displayed in 'Subject Compliance' is determined by the filter of the slicer Protocol ID and Site. Not affected by slicer Period screening.
3. The second column of 'ECG DataUpload' displayed for 'Subject Compliance' is an aggregate metric that represents the total number of data uploads for each Subject during the selected statistical Period as determined after the selected Protocol ID selected the Site.
   Measurement 'ECG DataUpload' is affected by the Protocol, Site, and Period of the slicer. 

ECG DataUpload = 
    -- Determine the slicer value
    VAR SelectedTenant = SELECTEDVALUE('TenantInformation'[Tenant])
    VAR SelectedSites = VALUES('Relation_SiteTenant'[Site])
    VAR SelectedPeriod = SELECTEDVALUE('Filter_PeriodTenant'[StatisticPeriod])
    -- Determine the date range of statistics according to the slicer Period
    VAR StartDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodStart], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    VAR EndDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodEnd], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    -- Build a temporary table to ensure that the Subject displays correctly
    VAR TempTable = ADDCOLUMNS(
        VALUES('Relation_SubjectSite'[Subject]),
        "ECGDataUpload", CALCULATE(
            SUM('DataUpload_Device_Subject-df'[amount]),
            FILTER('DataUpload_Device_Subject-df',
                'DataUpload_Device_Subject-df'[Tenant] = SelectedTenant && 'DataUpload_Device_Subject-df'[Site] IN SelectedSites &&
                'DataUpload_Device_Subject-df'[Subject] = EARLIER('Relation_SubjectSite'[Subject]) &&
                'DataUpload_Device_Subject-df'[StatisticDate] >= StartDate && 'DataUpload_Device_Subject-df'[StatisticDate] <= EndDate
            )
        )
    )
RETURN
SUMX(TempTable, IF(ISBLANK([ECGDataUpload]), 0, [ECGDataUpload]))​

• The third column 'ECG DataUpload Format' displayed for 'Subject Compliance' is also an aggregate metric that is the result of the content formatting displayed in the second column, representing the formatted value of the total number of data uploads for each Subject during the selected statistical Period as determined after the Protocol ID selected the Site.
  Measurement 'ECG DataUpload Format' is affected by the Protocol, Site, and Period of the slicer.

ECG DataUpload Format = 
    -- Determine the slicer value
    VAR SelectedTenant = SELECTEDVALUE('TenantInformation'[Tenant])
    VAR SelectedSites = VALUES('Relation_SiteTenant'[Site])
    VAR SelectedPeriod = SELECTEDVALUE('Filter_PeriodTenant'[StatisticPeriod])
    -- Determine the date range of statistics according to the slicer Period
    VAR StartDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodStart], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    VAR EndDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodEnd], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    -- Build a temporary table to ensure that the Subject displays correctly
    VAR TempTable = ADDCOLUMNS(
        SUMMARIZE('Relation_SubjectSite', 'Relation_SubjectSite'[Subject],
            "ECGDataUpload", CALCULATE(
                SUM('DataUpload_Device_Subject-df'[amount]),
                FILTER('DataUpload_Device_Subject-df',
                    'DataUpload_Device_Subject-df'[Tenant] = SelectedTenant && 'DataUpload_Device_Subject-df'[Site] IN SelectedSites &&
                    'DataUpload_Device_Subject-df'[StatisticDate] >= StartDate && 'DataUpload_Device_Subject-df'[StatisticDate] <= EndDate
                )
            )
        ),
        "TotalMinutes", IF(ISBLANK([ECGDataUpload]), 0, [ECGDataUpload]),
        "Days", INT(IF(ISBLANK([ECGDataUpload]), 0, [ECGDataUpload]) / 1440),
        "Hours", INT(MOD(IF(ISBLANK([ECGDataUpload]), 0, [ECGDataUpload]), 1440) / 60),
        "Minutes", MOD(IF(ISBLANK([ECGDataUpload]), 0, [ECGDataUpload]), 60)
    ) 
RETURN
MAXX(TempTable, [Days] & " days " & [Hours] & " hours " & [Minutes] & " minutes")​​

Help content

As shown in the image content of the Basic report style module, the Subject Compliance also needs to display a 'Rank' column.

The meaning of 'Rank' is that each Subject, determined after the Protocol ID and Site are selected, is ranked according to the metric 'ECG DataUpload' shown in the second column.
Theoretically expected Rank implementation effect is shown in the figure below:

Wrong Rank demonstration

I tried to build a few ‘Rank’ metrics myself, but there were a few issues that made it impossible to achieve the desired display.

Problem manifestation 1: The calculated Rank value is incorrect, all of which are 1; However, the slicer can take effect normally and does not affect the display of the Subject.

Rank = 
    -- Determine the slicer value
    VAR SelectedTenant = SELECTEDVALUE('TenantInformation'[Tenant])
    VAR SelectedSites = VALUES('Relation_SiteTenant'[Site])
    VAR SelectedPeriod = SELECTEDVALUE('Filter_PeriodTenant'[StatisticPeriod])
    -- Determine the date range of statistics according to the slicer Period
    VAR StartDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodStart], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    VAR EndDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodEnd], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    -- Build a temporary table to ensure that the Subject displays correctly
    VAR TempTable = ADDCOLUMNS(
        SUMMARIZE('Relation_SubjectSite', 'Relation_SubjectSite'[Subject],
            "ECGDataUpload", CALCULATE(
                SUM('DataUpload_Device_Subject-df'[amount]),
                FILTER('DataUpload_Device_Subject-df',
                    'DataUpload_Device_Subject-df'[Tenant] = SelectedTenant && 'DataUpload_Device_Subject-df'[Site] IN SelectedSites &&
                    'DataUpload_Device_Subject-df'[StatisticDate] >= StartDate && 'DataUpload_Device_Subject-df'[StatisticDate] <= EndDate
                )
            )
        ),
        "TotalMinutes", IF(ISBLANK([ECGDataUpload]), 0, [ECGDataUpload])
    )
RETURN
IF(ISBLANK([ECG DataUpload]),BLANK(),RANKX(TempTable,MAXX(TempTable, [TotalMinutes]),,DESC,DENSE))

Rank = 
    -- Determine the slicer value
    VAR SelectedTenant = SELECTEDVALUE('TenantInformation'[Tenant])
    VAR SelectedSites = VALUES('Relation_SiteTenant'[Site])
    VAR SelectedPeriod = SELECTEDVALUE('Filter_PeriodTenant'[StatisticPeriod])
    -- Determine the date range of statistics according to the slicer Period
    VAR StartDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodStart], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    VAR EndDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodEnd], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    -- Build a temporary table to ensure that the Subject displays correctly
    VAR TempTable = ADDCOLUMNS(
        SUMMARIZE(
            'Relation_SubjectSite',
            'Relation_SubjectSite'[Subject]
        ),
        "ECGDataUpload", CALCULATE(
            SUM('DataUpload_Device_Subject-df'[amount]),
            'DataUpload_Device_Subject-df'[Tenant] = SelectedTenant && 'DataUpload_Device_Subject-df'[Site] IN SelectedSites &&
            'DataUpload_Device_Subject-df'[StatisticDate] >= StartDate && 'DataUpload_Device_Subject-df'[StatisticDate] <= EndDate
        )
    )
    VAR CurrentECGDataUpload = CALCULATE(
        SUM('DataUpload_Device_Subject-df'[amount]),
        'DataUpload_Device_Subject-df'[Tenant] = SelectedTenant && 'DataUpload_Device_Subject-df'[Site] IN SelectedSites &&
        'DataUpload_Device_Subject-df'[Subject] = SELECTEDVALUE('Relation_SubjectSite'[Subject]) &&
        'DataUpload_Device_Subject-df'[StatisticDate] >= StartDate && 'DataUpload_Device_Subject-df'[StatisticDate] <= EndDate
    )    
RETURN
IF(
    ISBLANK(CurrentECGDataUpload),
    BLANK(),
    RANKX(
        TempTable,
        [ECGDataUpload],
        CurrentECGDataUpload,
        DESC,
        DENSE
    )
)

Problem manifestation 2: The calculated Rank value is incorrect, all of which are 1; In addition, after the display of Rank is increased in the diagram, the Subject screened by the slicer becomes invalid, and subjects that do not meet the screening appear.

Rank = 
    -- Determine the slicer value
    VAR SelectedTenant = SELECTEDVALUE('TenantInformation'[Tenant])
    VAR SelectedSites = VALUES('Relation_SiteTenant'[Site])
    VAR SelectedPeriod = SELECTEDVALUE('Filter_PeriodTenant'[StatisticPeriod])
    -- Determine the date range of statistics according to the slicer Period
    VAR StartDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodStart], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    VAR EndDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodEnd], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    -- Build a temporary table to ensure that the Subject displays correctly
    VAR TempTable = ADDCOLUMNS(
        VALUES('Relation_SubjectSite'[Subject]),
        "ECGDataUpload",CALCULATE(
            SUM('DataUpload_Device_Subject-df'[amount]),
            FILTER('DataUpload_Device_Subject-df',
                'DataUpload_Device_Subject-df'[Tenant] = SelectedTenant && 'DataUpload_Device_Subject-df'[Site] IN SelectedSites &&
                'DataUpload_Device_Subject-df'[Subject] = EARLIER('Relation_SubjectSite'[Subject]) &&
                'DataUpload_Device_Subject-df'[StatisticDate] >= StartDate && 'DataUpload_Device_Subject-df'[StatisticDate] <= EndDate
            )
        )
    )   
    -- Computes the ECGDataUpload for the current Subject
    VAR CurrentECGDataUpload = CALCULATE(
        SUM('DataUpload_Device_Subject-df'[amount]),
        FILTER('DataUpload_Device_Subject-df',
            'DataUpload_Device_Subject-df'[Tenant] = SelectedTenant && 'DataUpload_Device_Subject-df'[Site] IN SelectedSites &&
            'DataUpload_Device_Subject-df'[Subject] = SELECTEDVALUE('Relation_SubjectSite'[Subject]) &&
            'DataUpload_Device_Subject-df'[StatisticDate] >= StartDate && 'DataUpload_Device_Subject-df'[StatisticDate] <= EndDate
        )
    )
    -- If there is no data, set ECGDataUpload to 0
    VAR FinalECGDataUpload = IF(ISBLANK(CurrentECGDataUpload), 0, CurrentECGDataUpload)
RETURN
RANKX(TempTable, [ECGDataUpload], FinalECGDataUpload, DESC, DENSE)

Rank = 
    -- Determine the slicer value
    VAR SelectedTenant = SELECTEDVALUE('TenantInformation'[Tenant])
    VAR SelectedSites = VALUES('Relation_SiteTenant'[Site])
    VAR SelectedPeriod = SELECTEDVALUE('Filter_PeriodTenant'[StatisticPeriod])
    -- Determine the date range of statistics according to the slicer Period
    VAR StartDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodStart], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    VAR EndDate = LOOKUPVALUE('Filter_PeriodTenant'[PeriodEnd], 'Filter_PeriodTenant'[Tenant], SelectedTenant, 'Filter_PeriodTenant'[StatisticPeriod], SelectedPeriod)
    -- Build a temporary table to ensure that the Subject displays correctly
    VAR TempTable = ADDCOLUMNS(
        VALUES('Relation_SubjectSite'[Subject]),
        "ECGDataUpload",CALCULATE(
            SUM('DataUpload_Device_Subject-df'[amount]),
            FILTER('DataUpload_Device_Subject-df',
                'DataUpload_Device_Subject-df'[Tenant] = SelectedTenant && 'DataUpload_Device_Subject-df'[Site] IN SelectedSites &&
                'DataUpload_Device_Subject-df'[Subject] = EARLIER('Relation_SubjectSite'[Subject]) &&
                'DataUpload_Device_Subject-df'[StatisticDate] >= StartDate && 'DataUpload_Device_Subject-df'[StatisticDate] <= EndDate
            )
        )
    )  
RETURN
RANKX(TempTable,
    CALCULATE(SUMX(TempTable, [ECGDataUpload])),
    , DESC, DENSE
)

Problem manifestation 3: The calculated Rank value is correct; However, after the display of Rank is increased in the diagram, the Subject filtered by the slicer becomes invalid, and subjects that do not meet the filter appear.

Rank = RANKX(
    ALLSELECTED('Relation_SubjectSite'[Subject]),
    [ECG DataUpload],
    ,
    DESC,
    DENSE
)

Current status

I used 'Rank' for the third question type above and fulfilled the predetermined requirement by restricting the chart filter 'ECG DataUpload' to not be empty.

Rank = RANKX(
    ALLSELECTED('Relation_SubjectSite'[Subject]),
    [ECG DataUpload],
    ,
    DESC,
    DENSE
)

But in fact, the measurement formula of this Rank is itself wrong, and is a requirement that is implemented through other constraints.

How do you create a Rank measure that itself meets your requirements?

[Hugh-Guan · Accepted solution]

Hi everyone, 

The problem has been solved with the help of others.

The Rank expression is created as follows, without providing additional filter restrictions.

Rank Result = 
VAR CurVal = [ECG DataUpload]
VAR TempTable = 
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN(
                ALLSELECTED('Relation_SubjectSite'[Subject]),
                ALLSELECTED('Subject_FirstUpload-df'[first_upload])
                // ALLSELECTED('Table'[Column])
                // ...
                // If there are other dimension fields to be added to the matrix, please add them here
            ),
            "Val",
                [ECG DataUpload]
        ),
        NOT ISBLANK([Val])
    )
RETURN
IF(NOT ISBLANK(CurVal),RANKX(TempTable,[Val],CurVal,DESC,Dense))  

Help post the original link 
Hopefully this will help more people.
work smoothly, happy life!

[Hugh-Guan]

Hi Everyone， 

The previous report data link has expired. Update the link below:

Sample report.pbix      the current link is valid until 09:00:00 UTC on June 24, 2024.

[Hugh-Guan]

Hi Everyone， 

Maybe the problem description is abstract and not easy to understand. I will share the sample report data related to the question asked.
It seems that uploading attachments is not supported, so share via file link.
Sample report 
Each file link is valid for 3 days, and the current link is valid until 08:00:00 UTC on June 21, 2024.
If the link fails, I will update it in the reply. Thank you very much.

[Anonymous]

Hi @Hugh-Guan ,
According to your description, you want to implement the correct sorting and take the slicer values into account. Please provide detailed and complete example data so that we can help you faster. Please hide sensitive information in advance.

Best regards,
Albert He

[Hugh-Guan]

Hi @Anonymous ，
Thank you for your attention to the problem.
You mentioned that you may need detailed example data, I'm not sure if the following one meets your requirements.

Uploading attachments is not supported, so I put the sample report on the server, exposed by the URL.

Example Report share address 

[Hugh-Guan]

Sorry, I thought I had fulfilled the requirement by limiting chart filters, but when I added a new column to 'Subject Compliance', the Rank changed in a way I didn't understand.

The current Rank metric expression:

Rank = RANKX(
    ALLSELECTED('Relation_SubjectSite'[Subject]),
    [ECG DataUpload],
    ,
    DESC,
    DENSE
)

Report display

New unknown change

I have added a new column of 'first_upload' to the 'Subject Compliance' chart. The first column of Subject and the second 'ECG DataUpload' and the third 'ECG DataUpload Format' are not affected, but the 'Rank' value has changed.

The 'first_upload' is not a metric, but a column in the underlying table in the semantic model. And adding 'first_upload' to the graph does not change the results of the 'Subject' and metric 'ECG DataUpload'.