Custom Ranking for Sorting

[andimohr]

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.

[andimohr · Accepted solution]

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]

@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.

[andimohr]

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:

UID	RefID	Contract	Created	Type	Price
1234	111	101-D	19.05.2023	Revision Milestone	20 €
1235	111	101-D	19.05.2023	Prolongation Milestone	18 €
1236	111	101-D	09.05.2023	Change Project	20 €
1237	111	101-C	07.07.2022	Project	22 €
1238	111	101-B	07.07.2022	Change Project	22 €
1239	111	101-B	04.03.2022	Prolongation Contract	18 €
1240	111	101-B	04.11.2021	Prolongation 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

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

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

[andimohr]

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