Measure to get first matching text from a column (with custom filters) and show it in card visual

[volt26]

Hello!

I'm trying to do something that I thought was simple but I hit another wall...

I created a card with these filters on it :

it returns this (and it is the desired result) : 

The problem is, I want to create a measure that will return exactly that and I keep failing. Can you help me out please? Here is what sounded the most logical to me :

Measure = CALCULATETABLE(FIRSTNONBLANK(D_DATE[CYCLE_TEXTE_MOIS_J],D_DATE[CYCLE_TEXTE_MOIS_J]),D_DATE[ANNEE]="2024", D_DATE[CYCLE_SEQ]="1")

I get this on the card :


Thank you for your help!

[volt26]

Oooops!

That last formula did the trick. The only reason it didn't work is I had another filter going on that I forgot about.

Thank you for your help! It pointed me in the right direction!

[volt26 · Accepted solution]

I think I found the correct formula but somehow, the result is blank and it shouldnt!!

Measure =

CALCULATE(

    MIN('D_DATE'[CYCLE_TEXTE_MJ]),

    FILTER(D_DATE,[CYCLE_SEQ]="2")

)


If I look at the table, I can see there is no blank values. The MIN value I should get is "03-11 au 04-14" :

[v-tianyich-msft]

Hi @volt26 ,

I think on a small mistake, I did notice that 2024 is a number in your filter and the expression uses quotes to change it to a text type, so removing the quotes from 2024 in the expression would return the correct answer.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[volt26]

I know it sounds weird but this column is set as string in the database. I believe it is to allow the user to enter other stuff than a year... Unfortunately, I can't change anything about that.

[ryan_mayu]

maybe you can try this

Measure = CALCULATE(MIN(D_DATE[CYCLE_TEXTE_MOIS_J],D_DATE[CYCLE_TEXTE_MOIS_J]),D_DATE[ANNEE]="2024", D_DATE[CYCLE_SEQ]="1")

[volt26]

Unfortunately it doesn't work. It doesn't accept the 2nd D_DATE[CYCLE_TEXTE_MOIS_J]