Forum Discussion

YavuzDuran's avatar
YavuzDuran
Helper III
4 years ago
Solved

Custom Sorting for a Calculated Column

Hi all,

I have a slicer in which there is a calculated column value. 

I want to make a custom sort like:

Current

1 Dat PD

2 - 5 PD

6 - 15 PD

16 - 30 PD

31+ PD

 

but since this is a calculated column I could not do it on Power BI query editor since it is not there.

I tied adding a table and putting index and bringing that index near to this calculated column by LOOKUPVALUE, there occurs a circular dependency 

 

Any other way that can help me? 

 

 

Acct Standing = if(AllAccounts[Actual # Days Past Due]=0,"Current",
if(AllAccounts[Actual # Days Past Due]=1,"1 Day PD",
if(and(AllAccounts[Actual # Days Past Due]>1,AllAccounts[Actual # Days Past Due]<6),"2 - 5 PD",
if(and(AllAccounts[Actual # Days Past Due]>5,AllAccounts[Actual # Days Past Due]<16),"6 - 15 PD",
if(and(AllAccounts[Actual # Days Past Due]>15,AllAccounts[Actual # Days Past Due]<31),"16 - 30 PD",
if(AllAccounts[Actual # Days Past Due]>30,"31+ PD",""))))))

 

 

 

Need Help
  • mwegener's avatar
    mwegener
    4 years ago

    Hi YavuzDuran,

     

    for this you should use the function: "Sort one column by another column"

    https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?WT.mc_id=DP-MVP-5004288

     

    Acct Standing Index =
IF (
    AllAccounts[Actual # Days Past Due] = 0,
    0,
    IF (
        AllAccounts[Actual # Days Past Due] = 1,
        1,
        IF (
            AND (
                AllAccounts[Actual # Days Past Due] > 1,
                AllAccounts[Actual # Days Past Due] < 6
            ),
            2,
            IF (
                AND (
                    AllAccounts[Actual # Days Past Due] > 5,
                    AllAccounts[Actual # Days Past Due] < 16
                ),
                3,
                IF (
                    AND (
                        AllAccounts[Actual # Days Past Due] > 15,
                        AllAccounts[Actual # Days Past Due] < 31
                    ),
                    4,
                    IF ( AllAccounts[Actual # Days Past Due] > 30, 5, 99 )
                )
            )
        )
    )
)

2 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    4 years ago

    Hi YavuzDuran,

     

    for this you should use the function: "Sort one column by another column"

    https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?WT.mc_id=DP-MVP-5004288

     

    Acct Standing Index =
IF (
    AllAccounts[Actual # Days Past Due] = 0,
    0,
    IF (
        AllAccounts[Actual # Days Past Due] = 1,
        1,
        IF (
            AND (
                AllAccounts[Actual # Days Past Due] > 1,
                AllAccounts[Actual # Days Past Due] < 6
            ),
            2,
            IF (
                AND (
                    AllAccounts[Actual # Days Past Due] > 5,
                    AllAccounts[Actual # Days Past Due] < 16
                ),
                3,
                IF (
                    AND (
                        AllAccounts[Actual # Days Past Due] > 15,
                        AllAccounts[Actual # Days Past Due] < 31
                    ),
                    4,
                    IF ( AllAccounts[Actual # Days Past Due] > 30, 5, 99 )
                )
            )
        )
    )
)