Reply
andimohr
New Member

Custom Ranking for Sorting

Our aim is to get the price for each ID for the most recent date.

As we have different types, we want to weight the type accordingly to get the most relevant price if more than one type occurs on that day.

 

In our table in the tabular there are the following relevant columns:

 

ID,
Creation date,
Type,
price

 

If several types (text column) exist for a date, we would like to apply a ranking based on the assignment of a value so that we get the most relevant price for each ID in the result.

 

For the types "A", "B", "C", "D" we would like to assign different values, e g: A = 10, B = 15, C = 20, D = 25.

 

This means that if there are prices for type C and D on the most recent date, the price for type D should be displayed because it is weighted higher.

 

Because of the tabular, no grouping in advance etc. is possible, we would like the solution with a Dax-measure.

 

Do you have any ideas? We look forward to your help.

 

1 ACCEPTED SOLUTION
andimohr
New Member

I fixed it - here is the simple solution: so the issue is just on simple KEEPFILTERS in the right place. Check that in VAR PriceForReturn. I chose to publish a lengthy version of the code with additional VARS to make it more readable for entry level DAX enthusiasts - like me 🙂

Measure = 

VAR SelectedId =
    SELECTEDVALUE('contracts'[RefId])

VAR IdSubset =
    ADDCOLUMNS (
        CALCULATETABLE (
            'contracts',
            'contracts'[Type] IN { "Change Milestone", "Milestone", "Change Project", "Project" }
                && NOT ISBLANK ( 'contracts'[EK] )
                && 'contracts'[CreatedAtDate] <= MAX ( 'contracts_PersonSkillMap'[CreatedAtDate] )
                && 'contracts'[RefId] = SelectedMitarbeiter
        ),
        "RankBy",
        COMBINEVALUES(" ",
            FORMAT ( 'contracts'[CreatedAtDate], "yyyyMMdd" ),
            SWITCH (
                'contracts'[Type],
                "Change Milestone", "4",
                "Change Project", "3",
                "Project", "2",
                "Contract", "1",
                "0"
            ), 'contracts'[Price]
        ),      
        "Price Value",
        'contracts'[Price]
    )
    
VAR RankByForFilter = MAXX(IdSubset,[Rankby]) 

VAR PriceForReturn = 
    MAXX(
        FILTER( 
            KEEPFILTERS(IdSubset),
            [RankBy] = RankByForFilter
        ),
        [Price Value]
    )
    
RETURN 
    PriceForReturn


When you are new to DAX, coming from SQL, you have a lot to learn. Up to now I only considered that context is a big thing in DAX. Now I learned how essential - and at the same time tricky - context is.

Open is if I really need to add "Price Value" to the IdSubset... or if I could just return 

View solution in original post

4 REPLIES 4
andimohr
New Member

I fixed it - here is the simple solution: so the issue is just on simple KEEPFILTERS in the right place. Check that in VAR PriceForReturn. I chose to publish a lengthy version of the code with additional VARS to make it more readable for entry level DAX enthusiasts - like me 🙂

Measure = 

VAR SelectedId =
    SELECTEDVALUE('contracts'[RefId])

VAR IdSubset =
    ADDCOLUMNS (
        CALCULATETABLE (
            'contracts',
            'contracts'[Type] IN { "Change Milestone", "Milestone", "Change Project", "Project" }
                && NOT ISBLANK ( 'contracts'[EK] )
                && 'contracts'[CreatedAtDate] <= MAX ( 'contracts_PersonSkillMap'[CreatedAtDate] )
                && 'contracts'[RefId] = SelectedMitarbeiter
        ),
        "RankBy",
        COMBINEVALUES(" ",
            FORMAT ( 'contracts'[CreatedAtDate], "yyyyMMdd" ),
            SWITCH (
                'contracts'[Type],
                "Change Milestone", "4",
                "Change Project", "3",
                "Project", "2",
                "Contract", "1",
                "0"
            ), 'contracts'[Price]
        ),      
        "Price Value",
        'contracts'[Price]
    )
    
VAR RankByForFilter = MAXX(IdSubset,[Rankby]) 

VAR PriceForReturn = 
    MAXX(
        FILTER( 
            KEEPFILTERS(IdSubset),
            [RankBy] = RankByForFilter
        ),
        [Price Value]
    )
    
RETURN 
    PriceForReturn


When you are new to DAX, coming from SQL, you have a lot to learn. Up to now I only considered that context is a big thing in DAX. Now I learned how essential - and at the same time tricky - context is.

Open is if I really need to add "Price Value" to the IdSubset... or if I could just return 

Greg_Deckler
Super User
Super User

@andimohr Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thanx for your reply,

we have a large table with contract types that have different types. We want to compare the price of the most current contract with the price at a certain time in the past. For simplicity let´s only look at the measure for the most current contract. Here is some sample data:

 

UIDRefIDContractCreatedTypePrice
1234111101-D19.05.2023Revision Milestone 20 €
1235111101-D19.05.2023Prolongation Milestone 18 €
1236111101-D09.05.2023Change Project 20 €
1237111101-C07.07.2022Project 22 €
1238111101-B07.07.2022Change Project 22 €
1239111101-B04.03.2022Prolongation Contract 18 €
1240111101-B04.11.2021Prolongation Milestone 18 €

 

So for the most current contract we need the price. A custom sort order to weight the type is needed because alphabetical sort does not work and onl the date is not sufficient. In the example we want to get the 18€ for the Prolongation Milestone - not the 20€ for the Revision Milestone. And we want to use the outcome in visualisations
In the Tabular Model we have no attribut to "weight" the types. And for this requirement we do not want to implement additional attributes in the model. We are trying to solve this with DAX and not extend the PBI Model with an additional tables or columns.  

My idea was to rank the date plus the type with a switch to retrieve the price we desire for the various contract types... I have been working on a DAX...

 

Measure = 
VAR SelectedID =
    SELECTEDVALUE('contracts'[RefID])

VAR IdSubset =
    ADDCOLUMNS (
        CALCULATETABLE (
            'contracts',
            'contracts'[Type] IN { "Change Milestone", "Milestone", "Change Project", "Project" }
                && NOT ISBLANK ( 'contracts'[Price] )
                && 'contracts'[RefID] = SelectedID
        ),
        "RankBy",
        COMBINEVALUES(" ",
            FORMAT ( 'contracts'[Date], "yyyyMMdd" ),
            SWITCH (
                'contracts'[PlacementType],
                "Änderung Einsatzanweisung", "14",
                "Einsatzanweisung", "13",
                "Änderung Mitarbeitervertrag", "12",
                "Mitarbeitervertrag", "11",
                "10"
            ), 'contracts'[Price]
        )
        "getPrice",
        'contracts'[Price],
    )

RETURN 
    MAXX(IdSubset,[RankBy])

 


It works as long as I return the combined RankyBy value

andimohr_1-1708358845516.png

But when I return the contracts[Price] by itself the context somehow gets lost. To illustarte this I added the column "getPrice" with the same price to - what I suppose to be - a virtual table. But I do not get the 20,72 from the combined values - as if most of what I have done in the VAR is stripped and thrown over board in the RETURN - but at least its the MAX Price for the RefID and not the MAX overall price.

 

RETURN 
    MAXX(IdSubset,[getPrice])

 

 

andimohr_2-1708359119180.png


It might be my grid-way of thinking.... I hope this shows the problem well enough.


P.S the actual reason for the use of VAR SelectedId is to stay within a reasonable amount of data on the row-by-row calculation is: there are many RefId´s in the Contracts Table




some things I should have replaced in the code - I have a mix of sample data and real data - can´t edit the switch part but I hope the intention is clear

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)