Forum Discussion
Resolver IText calculated column filtered by value condition (power bi)
Hey guys can someone help me out with this please:
I have several Stock IDs from enterprises, being that an enterprise could have just a single value for its Stock ID, as well as various, having always a base of 4 initial letters (for example the enterprise with the Stock ID "AHEB" has AHEB3, AHEB5 and AHEB6).
My goal is to create a calculated column that already filters these IDs by negotiability (only the most negotiated ID for each enterprise should remain in this column). I'm sending a pbix example of the problem: https://file.io/Rg3izoPcapgW
Creating a new column with just the 4 initial letters as I did in the following example would be perfect for my other purposes if it showed also the rest of the ID (numbers/letters)
Ahh I got it grantsamborn! But the solution came from another way: I created these three formulas, and filtered my slicer with a Top N visual level filter, adding the variable "FILTERSTOCK" to it, so that it would filter only the Stock Codes that correspond to "TRUE". Here are the formulas, followed by the link with the file:
1) Last negotiated value column =VAR RESULT =CALCULATE(LASTNONBLANKVALUE('Database'[Date],max('Database'[negotiated value])))RETURN RESULT
2) MaxValuePerStock =VAR CurrentStock = max('Stock Codes'[Stock without numbers/letters])VAR RESULT =CALCULATE([last negotiated value column],FILTER(ALL('Stock Codes'), 'Stock Codes'[Stock without numbers/letters] = CurrentStock))RETURN RESULT3) FILTERSTOCK = if([last negotiated value column]=[MaxValuePerStock],TRUE(),BLANK())
File: https://we.tl/t-ADYymPKNDs
Thank you very much!!! 🙏😊
14 Replies
LucaixBolha
grantsambornSolution Sage
When trying to download your pbix, I'm told "The transfer you requested has been deleted."
- LucaixBolha
whoops, my bad! here it is grantsamborn : https://file.io/SwSi19PQGZgv
- grantsamborn
Still the same problem.
- grantsamborn
Take a look at this and let me know if it helps.
https://1drv.ms/u/s!AnF6rI36HAVkhPEtenBRgHzse3BGpA?e=hzCLio
Edited to add: After rereading your post, I realized you wanted a calculated column instead of measure(s).
- LucaixBolha
Hmm almost, thank you very much for your time! Maybe reformulating my goal would help to be clearer: I would like to have a slicer containing only one code per enterprise (more specifically the codes of the Stocks that are the most negotiated by value, like AHEB6, ALPA4, ALUP11, etc, on the most recent available data).
- grantsamborn
This time I created a calculated table by using the measures I previously wrote, and then created a many-to-one relationship between 'Database'[Stock] and 'zSummary'[Date].
https://1drv.ms/u/s!AnF6rI36HAVkhPEuzwSQhZibpIEm-A?e=RHtne6
Let me know if this helps.