Forum Discussion
Power BI DAX
Hello Community! I hope everyone is doing well, I am new to Power BI, and I am stuck with a situation where I have to sort this table By max date and by mission, where I search by CONTAINSTRING to get the missions starting by the same hashtag, and if multiple were found I should take the one corresponding to the max date here is what my original table looks like, and what my result table should look like;
Thank you in advance for your help.
Anonymous I added a supporting calculated column for the ouput matrix (column) called
#Hash = LEFT('Sample'[Mission],4)&"cell"I then created a measure as below
MaxDateValue = VAR SelectedID = SELECTEDVALUE ( 'Sample'[ID] ) VAR SeelctedHash = SELECTEDVALUE ( 'Sample'[#Hash] ) VAR MaxMissionDate = CALCULATE ( MAX ( 'Sample'[Missiong_Affectation_Date] ), FILTER ( 'Sample', 'Sample'[ID] = SelectedID && 'Sample'[#Hash] = SeelctedHash ) ) RETURN CALCULATE ( MAX ( 'Sample'[Mission] ), FILTER ( 'Sample', 'Sample'[ID] = SelectedID && 'Sample'[#Hash] = SeelctedHash && 'Sample'[Missiong_Affectation_Date] = MaxMissionDate ) )If I drag the "ID" and the "#Hash" on the rows and columns of a Matrix Visual and the MaxDateValue in the values the output is as follows
There might be a more efficient way to do this but this was certainly the quickest one I could think of without looking at the data model
4 Replies
- moizsherwani
Continued Contributor
Anonymous I added a supporting calculated column for the ouput matrix (column) called
#Hash = LEFT('Sample'[Mission],4)&"cell"I then created a measure as below
MaxDateValue = VAR SelectedID = SELECTEDVALUE ( 'Sample'[ID] ) VAR SeelctedHash = SELECTEDVALUE ( 'Sample'[#Hash] ) VAR MaxMissionDate = CALCULATE ( MAX ( 'Sample'[Missiong_Affectation_Date] ), FILTER ( 'Sample', 'Sample'[ID] = SelectedID && 'Sample'[#Hash] = SeelctedHash ) ) RETURN CALCULATE ( MAX ( 'Sample'[Mission] ), FILTER ( 'Sample', 'Sample'[ID] = SelectedID && 'Sample'[#Hash] = SeelctedHash && 'Sample'[Missiong_Affectation_Date] = MaxMissionDate ) )If I drag the "ID" and the "#Hash" on the rows and columns of a Matrix Visual and the MaxDateValue in the values the output is as follows
There might be a more efficient way to do this but this was certainly the quickest one I could think of without looking at the data model
- amitchandak
Super User
Anonymous , Create a new column in power query ->
Add column Tab -> extract -> Text before delimiter on mission column
Text Before Delimiter and Text After Delimiter: https://youtu.be/oGY4RoPKDOE
that will give #AB #DD etc, If needed add suffix
Power Query- Add Prefix and add suffix: https://youtu.be/RgazgGYDqPQ
you can use that column in Matrix
