Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Impactful Individual
6 years ago
Solved

RANKX with USERELATIONSHIP

Hi all,

 

i have the following problem sitting on it for a long time:

 

Rank_Trader = RANKX(Table1; Table1[Trader]; ; ASC; Dense)
 
On the FilterPane I filter the year = 2020 which gives me a wrong result, because: 
My Table1 has an active relationship with the Calender via Date <---> Table1_Date1.
But for the Rank calculation I want to use the relationship Date <---> Table1_Date2.
 
Some ideas how to integrate USERELATIONSHIP(Calender[Date];Table1[Date2]) in the RANKX formula?
 
I tried to shorten the problem. May the wrong result makes no sense in this context. But RANKX with USERELATIONSHIP will may help.
 
Many thanks.
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Pikachu-Power ,

     

    Why do you create your measure like below? I don't quite understand.

    Measure = CALCULATE(COUNT(Trader[Date2]),USERELATIONSHIP('Calendar'[Date],Trader[Date2]))

     

    Just create your measure like so:

    Measure = COUNT(Trader[Date2])

    Then, your [Sort_Condition]:

     

    Or, you can convert [Sort_Condition] to a measure:

    Sort_Condition Measure 1 = 
IF (
    MAX ( Trader[Trader] ) <> BLANK (),
    RANKX (
        FILTER ( ALLSELECTED ( Trader ), Trader[Trader] <> BLANK () ),
        CALCULATE ( MAX ( Trader[Trader] ) ),
        ,
        ASC,
        DENSE
    ) * 0.00001 + [Measure]
)
    Sort_Condition Measure 2 = 
RANKX (
    FILTER ( ALLSELECTED ( Trader ), Trader[Trader] <> BLANK () ),
    CALCULATE ( MAX ( Trader[Trader] ) ),
    ,
    ASC,
    DENSE
) * 0.00001 * [Measure] + [Measure]

    BTW, .pbix file attached.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Pikachu-Power ,

    some thing like this. But this Rank seems like column. and has no role of date

    calculate(RANKX(Table1; Table1[Trader]; ; ASC; Dense),USERELATIONSHIP(Calender[Date];Table1[Date2]) )

     

    You can create a measure

    cnt =

    calculate(count(Table1[Trader]),USERELATIONSHIP(Calender[Date];Table1[Date2]) )

     

    and have rank on that

    RANKX(Table1; [cnt]; ; ASC; Dense)