Forum Discussion
Custom Ranking for Sorting
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
Community Championandimohr 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.
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
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