cancel
Showing results for
Did you mean:

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

Regular Visitor

## Modify the DAX for LASTNONBLANKVALUE for a Non Date Context

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

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:

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
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])))

2 REPLIES 2
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])))

Regular Visitor

@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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors