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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Amaze23
Regular Visitor

Modify the DAX for LASTNONBLANKVALUE for a Non Date Context

Hello,
There is a DAX query which i am unable to resolve.

Amaze23_0-1642607406335.png

 


Background of the screenshot:  In the above screenshot we have date for conferences that happens almost every year.

Conf Code - Conference Code assigned to a conference for a particular year and changes slightly every year
Accu Code - It is unique to every Conference and remains same.
Conf Year - Year in which conference has happened.
# Individuals Retained - it represents the no. of attendees got retained.
# Individuals Retained PY - it represents the no. of attendees got retained from past year.

 
Query & Desired Result:  Amaze23_1-1642607406347.png

 

Suppose a new conference "CET22A" happens this year i.e. "2022" but the "# Individuals Retained" measure is blank then "# Individuals Retained PY" measure should show the last non blank value of "# Individuals Retained" measure which is 24 in above case and repeat it until "# Individuals Retained" has a value as in the screenshot for this particular conference only.

Current Measure in use:

# of Individuals Retained PY =
var py = max('Dim - Conferences'[Conf Year]) -1
var acc = MAX('Dim - Conferences'[Accu Code])
return
if(HASONEVALUE('Dim - Conferences'[Conf Code]),
CALCULATE( [# of Individuals Retained],
filter(all('Dim - Conferences'),
'Dim - Conferences'[Conf Year]= py && 'Dim - Conferences'[Accu Code]=acc) ),
CALCULATE([# of Individuals Retained],
filter(all('Dim - Conferences'),
'Dim - Conferences'[Conf Year]= py )))

I cant share the data due to privacy reasons.

Best Regards

#DAX #Desktop #DAXissues
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Amaze23 , Try a measure like

calculate(lastnonblankvalue(Table[Year],[# Individuals Retained]), filter(all(Table), [Accu Code] = max([Accu Code] ) && [Year] <= Max(Table[Year])))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Amaze23 , Try a measure like

calculate(lastnonblankvalue(Table[Year],[# Individuals Retained]), filter(all(Table), [Accu Code] = max([Accu Code] ) && [Year] <= Max(Table[Year])))

@amitchandak : Thanks, it worked great! you used lastnonblankvalue & tweaked use of conf year as Conf year <= Max (conf year), can you put light on this ?

Actually it looks simple but i am not able to get it.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.