topic Re: Replce blank with last value in dax in DAX Commands and Tips

Replce blank with last value in dax

F_mmb — Mon, 24 Jun 2024 07:22:07 GMT

I have a table that includes order details, with columns for order year and category as shown in the sample below:

Id	category	Year
1	x	2020
2	x	2020
3	y	2020
4	y	2020
5	y	2020
8	z	2020

I want to count the cumulative number of orders I have per year and category. So, I created the following measures.

IDCount = COUNT(Fact[Id])

CumulativeIDCount = CALCULATE( SUMX( FILTER( ALL(Fact), Fact[Year] <= MAX(Fact[Year]) && Fact[category] = SELECTEDVALUE(Fact[category]) ), [IDCount] ) )

Then, I used a matrix to present the results, and it is quite what I want. However, for empty values, I want to replace the value with the last value before it.

the result:

cetegory	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024
x									1
y	34	84	90	46	70	42	84	79	57	4473	4516
z	6			7
f			13						14
t	18	19			20	24		25	32	33

the result I expected:

cetegory	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024
x									1	1	1
y	34	84	90	46	70	42	84	79	57	4473	4516
z	6	6	6	7	7	7	7	7	7	7	7
f			13	13	13	13	13	13	14	14	14
t	18	19	19	19	20	24	24	25	32	33	33

I need your help to modify the current DAX measure to handle blank values as fill down approach.

Re: Replce blank with last value in dax

lbendlin — Wed, 26 Jun 2024 00:36:26 GMT

However, for empty values, I want to replace the value with the last value before it

To report on things that are not there you need to use disconnected tables and/or crossjoins

Re: Replce blank with last value in dax

Anonymous — Wed, 26 Jun 2024 08:42:40 GMT

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi F_mmb ,

Here are the steps you can follow：

1. Create measure.

Test = var _year= MAXX( FILTER(ALL('Table'), 'Table'[Category]=MAX('Table'[Category])&& [CumulativeIDCount]<>BLANK()&&'Table'[Year]<MAX('Table'[Year])),[Year]) return IF( [CumulativeIDCount]=BLANK(), SUMX( FILTER(ALL('Table'), 'Table'[Category]=MAX('Table'[Category]) && 'Table'[Year]=_year&&[CumulativeIDCount]<>BLANK() ),[CumulativeIDCount]), [CumulativeIDCount])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Replce blank with last value in dax

F_mmb — Thu, 04 Jul 2024 05:27:00 GMT

Thank you. The issue is that IDCount is not a column; it is a measure, as I mentioned in the post.