Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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!
Solved! Go to 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.
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
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.
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
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
@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.
unfortunately not, already tried it before and gave me date & time instead