How to get last row value which is not blank for Card Visual

[bix]

Dear All

I want to fetch the last row of a coloumn value which is not blank as shown below table , on the total employees card , i need to show the value od March 2021 , I created index coloumn starting from 1 to 12 , and I used the DAX as below 

NOOFEMPLOYEES = CALCULATE(LASTNONBLANK(DATA[EMP],1),FILTER(DATA,MainData1[Index]=MAX(DATA[Index])))

But its still gives me Blank value , the EMP coloumn set as distinct  format

MONTH	NUMBER OF EMPLOYEES
Jan 2021	143
Feb 2021	156
March 2021	160
April 2021	0 (null)

[v-jingzhang · Accepted solution]

Hi @bix 

0 is not blank. But when a column is of number type, blank equals to 0. So you could use below measure. Or you could use ">" to replace "<>" as the number of employees wouldn't be negative. 

NOOFEMPLOYEES = 
VAR _index = MAXX(FILTER('DATA','DATA'[NUMBER OF EMPLOYEES]<>0),'DATA'[Index])
RETURN
MAXX(FILTER('DATA','DATA'[Index]=_index),'DATA'[NUMBER OF EMPLOYEES])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Pragati11]

Hi @bix ,

Your DAX references 2 different tables. Is the data shared above not in a single table in Power BI?

Thanks,

Pragati

[bix]

Hi it is same table its just a mistake when i typed the dax formula

[parry2k]

@bix check this video on my youtube channel  https://youtu.be/0-5-Y_A5wfw

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.