How to display the firstnonblank value of an existing measure?

[queryuser]

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!

[amitchandak · Accepted solution]

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

[v-yalanwu-msft]

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.

[queryuser]

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

[queryuser · Accepted solution]

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

[amitchandak · Accepted solution]

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

[queryuser]

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