- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
12-07-2024 01:31 AM | |||
12-19-2023 03:24 AM | |||
11-21-2024 04:58 AM | |||
02-13-2017 12:44 PM | |||
09-14-2022 08:30 AM |
User | Count |
---|---|
23 | |
12 | |
10 | |
10 | |
8 |
User | Count |
---|---|
16 | |
15 | |
15 | |
12 | |
10 |