Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"
)
)
Solved! Go to Solution.
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
)
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 🙂
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.
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
)
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
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |