Forum Discussion

kk_shp_user's avatar
kk_shp_user
Helper I
1 year ago
Solved

Computing Rank using 2 measures

I have a data model in which I have created two measures,  m_personal_recruits -> tells how many recruits have been hired by a manager m_personal_recruit_qv -> tells how much sales the new recruits...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    I think the id column is causing the issue. You'll need to add that to the ALLSELECTED, e.g.

    Rank =
VAR BaseTable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                ALLSELECTED ( 'Table'[Manager], 'Table'[id] ),
                "@PersonalRecruits", [m_personal_recruits]
            ),
            [@PersonalRecruits] > 0
        ),
        "@QV", [m_personal_recruits_qv]
    )
VAR Result =
    RANK ( BaseTable, ORDERBY ( [@PersonalRecruits], DESC, [@QV], DESC ) )
RETURN
    Result

    You'll also need to add any other columns from the same table which are in the visual.

kk_shp_user's avatar
kk_shp_user
Helper I
1 year ago

Hi johnt75 ,

Thanks for this. The table is a huge one and so the query exceeds resources. Is there anyway to filter the data first to get only records that have [m_personal_recruits]>0 and then to rank?

I tried to filter within ALLSELECTED() but in vain.

johnt75's avatar
johnt75
Super User
1 year ago

Try

Rank =
VAR BaseTable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                ALLSELECTED ( 'Table'[Manager] ),
                "@PersonalRecruits", [m_personal_recruits]
            ),
            [@PersonalRecruits] > 0
        ),
        "@QV", [m_personal_recruits_qv]
    )
VAR Result =
    RANK ( BaseTable, ORDERBY ( [@PersonalRecruits], DESC, [@QV], DESC ) )
RETURN
    Result
  • kk_shp_user's avatar
    kk_shp_user
    Helper I
    1 year ago

    Thanks, I tried this and also tried the same solution with a few different formats before your message. I can make it work for my purpose by using the rank column as a filter on Manager to filter by Bottom N.

    However if I pop it in the visual, ranks for all Managers are 1.

    Below is the screenshot where I have filtered by Bottom 5 (since I want ranks from 1 to 5)

     






    • johnt75's avatar
      johnt75
      Super User
      1 year ago

      I think the id column is causing the issue. You'll need to add that to the ALLSELECTED, e.g.

      Rank =
VAR BaseTable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                ALLSELECTED ( 'Table'[Manager], 'Table'[id] ),
                "@PersonalRecruits", [m_personal_recruits]
            ),
            [@PersonalRecruits] > 0
        ),
        "@QV", [m_personal_recruits_qv]
    )
VAR Result =
    RANK ( BaseTable, ORDERBY ( [@PersonalRecruits], DESC, [@QV], DESC ) )
RETURN
    Result

      You'll also need to add any other columns from the same table which are in the visual.