Forum Discussion

onedayover's avatar
onedayover
Helper III
2 years ago
Solved

Replacing blanks with previous count in continuous line chart

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

blanks
Help
Line chart
  • aduguid's avatar
    aduguid
    2 years ago
    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
    )

4 Replies

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago
    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
    )
    • onedayover's avatar
      onedayover
      Helper III
      2 years ago

      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

  • onedayover's avatar
    onedayover
    Helper III
    2 years ago

    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 🙂

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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.