topic Replacing blanks with previous count in continuous line chart in DAX Commands and Tips

Replacing blanks with previous count in continuous line chart

onedayover — Mon, 29 Apr 2024 00:45:04 GMT

Hi All

I'm hoping someone will be able to help me modify this measure. I've tried a number of ways to make it work but can't quite seem to get it right. Where there is a blank week (Wk 9 in this example, I would like the figure from the previous week, e.g. 100 (Wk 8), so that my line is continuous and doesn't drop to 0. My measure so far is:

2024_cate_tickets =
COUNTROWS (
FILTER (
ALL ( 'cate' ),
'cate'[week_num] <= MAX ( 'cate'[week_num] )
&& 'cate'[date_registered] <= MAX ( 'cate'[date_registered] )
&& 'cate'[event] = "cate 2024"
)
)

Re: Replacing blanks with previous count in continuous line chart

aduguid — Mon, 29 Apr 2024 03:01:34 GMT

2024_cate_tickets = VAR CurrentCount = COUNTROWS ( FILTER ( ALL ( 'cate' ), 'cate'[week_num] <= MAX ( 'cate'[week_num] ) && 'cate'[date_registered] <= MAX ( 'cate'[date_registered] ) && 'cate'[event] = "cate 2024" ) ) RETURN IF( ISBLANK(CurrentCount), CALCULATE( LASTNONBLANK('cate'[week_num], CurrentCount), FILTER(ALL('cate'), 'cate'[week_num] < MAX('cate'[week_num])) ), CurrentCount )

Re: Replacing blanks with previous count in continuous line chart

onedayover — Mon, 29 Apr 2024 03:15:02 GMT

Hi aduguid,
Thank you for your reply.
I just tried your code and it the graph didn't change. Do you have any further suggestions?
Thanks

Re: Replacing blanks with previous count in continuous line chart

Jihwan_Kim — Mon, 29 Apr 2024 04:58:45 GMT

Hi,

I am not sure how your semantic model looks like, but I assume you are not using dimension table, or I assume cate table does not contain wk9 data.

Please try using date dimension table.

1. Create date dimension table.

2. Create a relationship between date dimension table & cate table

3. Use x-axis from date dimension table

4. In the measure, input column names from date dimension table.

Re: Replacing blanks with previous count in continuous line chart

onedayover — Wed, 08 May 2024 04:53:58 GMT

Hi aduguid, apologies for the delay in getting back to you. You were correct on both counts - I wasn't using a dimension table and the cate table did not contain wk9 data. I fixed this as you outlined and got the graph to display exactly how I needed it to. Thanks again for your help 🙂