Text calculated column filtered by value condition (power bi)

[LucaixBolha]

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)

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

[grantsamborn]

Same except, I removed the measures making it cleaner.

https://1drv.ms/u/s!AnF6rI36HAVkhPEvjDvTOr_cQSaooQ?e=UG6TMu

[LucaixBolha · Accepted solution]

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 RESULT

3) FILTERSTOCK = if([last negotiated value column]=[MaxValuePerStock],

TRUE(),

BLANK())

File: https://we.tl/t-ADYymPKNDs

Thank you very much!!! 🙏😊

[grantsamborn]

Hi

I'm not sure how much I helped but glad you got it figured out.

[LucaixBolha]

Ohh almost perfect @grantsamborn, thank you very much!! For a great part of them your code recognized the right Stock Code to be displayed, but for some of them not yet. For example ALUP should be ALUP11 instead of ALUP4 , BDLL3 instead of 4, and other cases like BRAP99, that is appearing in the slicer, but not in the table (didn't quite understand what happenned with this one 🤔).  

[grantsamborn]

In your initial description of the problem, you said "only the most negotiated ID for each enterprise should remain in this column."  Can you explain this?

When 2 (or more) Stocks for a single Enterprise appear on the latest (non-blank) date, I'm not sure which to use.

Did you mean to use the row count per Stock as the tie-breaker?

[grantsamborn]

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.

[LucaixBolha]

strange! Try this one out please @grantsamborn : https://we.tl/t-8q01qvOkFj

[grantsamborn]

OK - that link worked.

[LucaixBolha]