Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GilbertQ
Super User
Super User

Get LastNonBlank Value across all rows

Hi there I wonder if someone could help me out.

 

If you can refer to the image below.

 

LastNonBlank.png

What I want is the Value from the ZCALC Rowz (30/06/2019) to be repeated. As is shown the the example in the ZCALC ROwz column (1/07/2020)

 

The trick here is that I have got a Fiscal Year Slicer, so if I click on Fiscal Year 2019-20 then I want the repeated value to change from 30/06/2019 to be 30/06/2020 for all the rows in the table.

 

Any help or suggestions will be appreciated.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

1 ACCEPTED SOLUTION

Hi @tringuyenminh92 I managed to figure it out

 

Here is the code that I used to return to me the values selected from the Fiscal Year Slicer

 

ZCALC ROwz2 =
CALCULATE (
    CALCULATE ( COUNTROWS ( 'TYOY' )ALLSELECTED ( 'Date'[Fiscal Year] ) ),
    ALLSELECTED ( 'Date' )
)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

4 REPLIES 4
tringuyenminh92
Memorable Member
Memorable Member

Hi @GilbertQ,

 

I'm sorry but i'm trying to understand your case correctly. Could you please provide the picture of whole report you want and data table with Fields in chart? If there is no sensitive information, you could share current pbix file. So I could quicky understand and help you.

Hi @tringuyenminh92 I managed to figure it out

 

Here is the code that I used to return to me the values selected from the Fiscal Year Slicer

 

ZCALC ROwz2 =
CALCULATE (
    CALCULATE ( COUNTROWS ( 'TYOY' )ALLSELECTED ( 'Date'[Fiscal Year] ) ),
    ALLSELECTED ( 'Date' )
)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

It's good to hear that. Another way to get current selected in slicer is using FirstNonBlank( Values( filter column),1)

That does work, but when you have all your dates in the table, it shows all the dates in their row context. So whilst that would work if there was no Fiscal Year in the rows, in my dataset there was. So for each row the FirstNonBlank Date in the row context is the Fiscal Year.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.