cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gwyn
New Member

Using only the top of a between date slicer.

Hi,

I'm looking to create a LASTNONBLANK measure for a column in a table visual. It works fine, but if the user moves the 'date between' slicer min(date) to beyond when the lastnonblank was logged it goes blank. 

I need the max(date) in the date slice to still effect to LASTNONBLANK measure. Is there some way to have the date table that the LASTNONBLANK measure scans over ignore the min(date) slicer values for the page?

 
DIGI STATUS =
VAR _START_TIMEALL ( 'T_DATE'[DATE] )  -- This to ignore all slicers on the date
VAR _END_TIMEMAX ( 'T_DATE'[DATE] ) -- This to still allow the max(date) slicer

RETURN
LASTNONBLANKVALUE(
DATESBETWEEN('T_DATE'[DATE]_START_TIME_END_TIME),   -- This is the bit where I fall down
MAX('HUBSPOT_HISTORIC_STATUS'[DIGITAL_ADVERTISING_STATUS])
)
 
There is a relationship between the dates table 'T_DATE' and the 'HISTORIC_STATUS' table.
 
4 REPLIES 4
AllisonKennedy
Super User
Super User

@gwyn I'm not quite sure what you're trying to acheive, but the ALL() function returns an entire table and you need a single scalar value. Dax.Guide is a great resource for what each function does. 

 

Try using MINX for your start time: 

 

DIGI STATUS =
VAR _START_TIME = MINX(ALL ( 'T_DATE'[DATE] ) ,'T_DATE'[DATE] )  -- This to ignore all slicers on the date
VAR _END_TIME = MAX ( 'T_DATE'[DATE] ) -- This to still allow the max(date) slicer

RETURN
LASTNONBLANKVALUE(
DATESBETWEEN('T_DATE'[DATE]_START_TIME_END_TIME),   -- This is the bit where I fall down
MAX('HUBSPOT_HISTORIC_STATUS'[DIGITAL_ADVERTISING_STATUS])
)

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thank you, If i could send you a beer virtually I would.

@gwyn  Best reply ever - much thanks. Did you solve your problem then or still have more questions?


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

All Sorted, I was clearly just trying to jam the whole (ALL(T_DATE) table into the DATESBETWEEN function where it wanted the MINX date value. I have successfully worked around the lower part of the slicer.  🍻

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors