topic How to display the firstnonblank value of an existing measure? in DAX Commands and Tips

How to display the firstnonblank value of an existing measure?

queryuser — Thu, 03 Feb 2022 13:06:38 GMT

Hello,

Maybe someone could help.

I would like to display the firstnonblank value from a dax measure which is displayed as column "Which Product Exceeds Forecast" in a card.

What was managed:

to get the first date when sales > forecast in a card because the filtered column was date
to get the Sales Rep based on which Sales Rep has exceeded each other end the forecast

What I am struggling with, is to display in a card the firstnonblank value from the last column which is a measure itself

Many thanks in advance!

Re: How to display the firstnonblank value of an existing measure?

amitchandak — Thu, 03 Feb 2022 16:08:57 GMT

queryuser , is

firstnonblankvalues(Table[Date], [Which Product Exceeds Forecast])

not work ?

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: How to display the firstnonblank value of an existing measure?

queryuser — Thu, 03 Feb 2022 16:17:14 GMT

unfortunately not, already tried it before and gave me date & time instead

Re: How to display the firstnonblank value of an existing measure?

queryuser — Wed, 09 Feb 2022 09:05:02 GMT

Hi,

I found a solution by creating 4 measures.

M1 =if(and(sales rep 1 > sales rep 2, sales rep 1 > forecast, "sales rep 1",(if(and(sales rep 2 > sales rep 1, sales rep 2 > forecast, "sales rep 2")

M2 = Max (sales rep 1, sales rep 2)

M3 = if ( M2 > forecast, M1, Blank())

M4 = FIRSTNONBLANKVALUE(Date, M3 )

Best regards

Re: How to display the firstnonblank value of an existing measure?

v-yalanwu-msft — Wed, 09 Feb 2022 09:58:19 GMT

Hi, queryuser ;

You could create a measure or use first value in card .

1.create a measure.

exceeded date = IF(MAX([Date])= CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Sales Rep 1]>[Sale Forecast]||[Sales Rep 2]>[Sale Forecast]))||MAX([Date])=MAXX(ALL('Table'),[Date]),MAX([Date]))

which = IF([exceeded date]<>BLANK(), IF(MAX([Date])<MAXX(ALL('Table'),[Date]), IF( CALCULATE(SUM('Table'[Sales Rep 1]),FILTER('Table',[Date]=[exceeded date]))>SUM('Table'[Sale Forecast]),"Sales Rep1","Sales Rep2"), IF(SUM('Table'[Sales Rep 1])>SUM('Table'[Sales Rep 2]),"Sales Rep1","Sales Rep2")))

Card = CALCULATE([which],FILTER('Table',[Date]= MinX(FILTER(ALL('Table'),[which]<>BLANK()),[Date])))

2.Or use a first value.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to display the firstnonblank value of an existing measure?

queryuser — Wed, 09 Feb 2022 10:01:20 GMT

Hello,

Thanks for the effort! Will take a look at the solution in more detail.

For the moment will keep the actual measures in place.

Best regards