[Need Help] Add a dynamic rank column to a chart of type Table

[Hugh-Guan]

Hi Everyone,

Help content

As shown below I want to add a 'Rank' in the chart 'Subject Compliance'. However, I have been creating errors in the Rank metric and would like your help to create the correct Rank.

In theory 'Rank' is dynamic and is affected by the filtering results of the slicer 'Protocol ID', 'Site' and 'Period'.

Chart description

1. 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.
2. 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 ID', 'Site', and 'Period' of the slicer. 
3. 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.

Report data PBIX

Demo-001.pbix    the current link is valid until 07:00:00 UTC on June 29, 2024.

If no direct upload attachment is found, upload the report as a URL. Later, if the URL expires, I will re-upload the data link.

Any help would be appreciated!

[qwasze · Accepted solution]

Hi @Hugh-Guan 

I edited your DAX formula, you are almost there:

RANKX(

    ALL('Relation_SubjectSite'),

    [ECG DataUpload],

    ,

    DESC,

    DENSE

)
This should solve your issue.

[xifeng_L · Accepted solution]

Hi @Hugh-Guan ,

You can try below measure, it may meet your need.

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))    

Demo-Add a dynamic rank column to a chart of type Table.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Hugh-Guan]

Hi @xifeng_L ，

Thank you for your help.

I tried the Rank expression you gave and it solved the problem perfectly without having to give an additional filter.

I noticed the use of "CROSSJOIN" in the formula, which may magnify the operation time when the amount of table data is too large. At present, its operation response is still very fast.

Of course, this is probably the best solution based on the current semantic model and design requirements.

Thank you again for your help.

work smoothly, happy life!

[Hugh-Guan]

Hi everyone,

I updated the sample data for the report.

Demo-002.pbix   the current link is valid until 09:20:00 UTC on June 30, 2024.

Any help would be appreciated!

[Hugh-Guan]

Hi everyone,

I updated the sample data for the report.

Demo-002.pbix   the current link is valid until 09:20:00 UTC on June 30, 2024.

Any help would be appreciated!

[qwasze · Accepted solution]

Hi @Hugh-Guan 

I edited your DAX formula, you are almost there:

RANKX(

    ALL('Relation_SubjectSite'),

    [ECG DataUpload],

    ,

    DESC,

    DENSE

)
This should solve your issue.

[Hugh-Guan]

Hi @qwasze ，

Thank you for your help.
The problem has been solved. The Rank expression is created as follows, and no additional filter restrictions are required.

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))    

I hope this helps you, too.

work smoothly, happy life!

[Hugh-Guan]

Hi @qwasze ,

I'm sorry I gave you the wrong feedback.

The Rank expression provided is problematic and does not fulfill the predetermined requirement.It will cause the slicer to fail to filter the Subject, and the Subject that does not meet the filtering conditions will be displayed.

These subjects should not appear under the TNX-103-06 screening criteria. 

[qwasze]

Hi @Hugh-Guan , this has something to do with your table relationships. It has nothing to do with the rank formula. A dax measure does not affect the rank unless you put it as a slicer. To solve your problem, I have created another DAX measure that does the opposite way:

Rank01 Asc = RANKX(

    ALL('Relation_SubjectSite'),

    [ECG DataUpload],

    ,

    ASC,

    DENSE

)

I then filter the visual by  this:

This will help to filter for those which do not belong to the following subject I think( I noted that you have many to many relationships and this could be a result)

[Hugh-Guan]

Hi @qwasze ,

Sorry for not replying to your message in time.
I tried the method you gave me and created 'Rank01 Asc'. It simply filters out data with 'ECG DataUpload' equal to the minimum value 0, and does not dynamically restrict the data with the value of 'Protocol ID'.

I tried another approach, creating a computed column 'SelectedFlag' and limiting the display data by filtering its value.

SelectedFlag = 
VAR SelectedTenant = SELECTEDVALUE('TenantInformation'[Tenant])
RETURN 
IF (
    NOT(ISBLANK(SelectedTenant)) && CONTAINSSTRINGEXACT('Relation_SubjectSite'[T&S], SelectedTenant),
    TRUE,
    FALSE
)

In theory this should have worked, but it failed. I'm not sure if it's inspiring for you.

work smoothly, happy life!

[Hugh-Guan]

Hi @qwasze ，

Thank you very much for your help. I have tested the DAX expression you provided and it works.

I'm sorry, but I still have a few questions that I hope will be answered. Of course, from the perspective of requirements implementation, you already have a good implementation of the functional requirements.

1. The currently provided Rank expression also needs to be combined with a restriction filter condition to meet the requirements. In theory, the result of this Rank expression itself does not meet the requirements. Is this aspect of the requirement itself not achievable through this semantic model? Or is the Rank expression not perfect?

         

1.  I tested another Rank expression earlier, and it works under certain circumstances. But when the 'Actual Enrolled Date' column was added to the 'Subject Compliance' chart, something changed that I didn't understand. Is it possible to explain why.

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

Thank you again for your help!

[qwasze]

Hi @Hugh-Guan , I believe I explained 1 to a certain extent above. In short, the reason for this restriction filter is due to the relationships determined in your dataset that is causing this to happen. It might be better to create a star schema approach in modelling that can better help you.

For 2), when you add an actual enrolled date to the subject compliance chart, it creates a problem in the ranking. Allselected means that you are filtering the data for subject based on your current filters and when you add an enrolled date, it suddenly comes out to many different enrolled dates for the same subject which leads to rank not performing. I do not know how to better explain it, but rank is a very complicated thing haha.