No values on stacked area chart (instead of 0)

[Anonymous]

I have problem with stacked area chart. When measure return blank values when there is no values on plot:

My measure: 

%_Time_avg_per_case_in_hours =

DIVIDE ([Time_avg_per_case_in_hours], (

CALCULATE([Time_avg_per_case_in_hours], FILTER(ALL(tasks), tasks[task productivity] = "Productive")) +

CALCULATE([Time_avg_per_case_in_hours], FILTER(ALL(tasks), tasks[task productivity] = "Unproductive"))),0)

I tried different options:

%_Time_avg_per_case_in_hours =

DIVIDE ([Time_avg_per_case_in_hours], (

CALCULATE([Time_avg_per_case_in_hours], FILTER(ALL(tasks), tasks[task productivity] = "Productive")) +

CALCULATE([Time_avg_per_case_in_hours], FILTER(ALL(tasks), tasks[task productivity] = "Unproductive"))),0) + 0

%_Time_avg_per_case_in_hours =

DIVIDE (IF( ISBLANK([Time_avg_per_case_in_hours]),0, [Time_avg_per_case_in_hours], (

CALCULATE([Time_avg_per_case_in_hours], FILTER(ALL(tasks), tasks[task productivity] = "Productive")) +

CALCULATE([Time_avg_per_case_in_hours], FILTER(ALL(tasks), tasks[task productivity] = "Unproductive"))),0) + 0

but it is not working and returns:

Do you have idea why is not working?

[v-lili6-msft]

hi @Anonymous 

You need to create a dim date table and then create a relationship with tasks table by date column and use date column from dim date table for the x-axis value, then it should be ok.

If not your case, please share your sample pbix file for us have a test, that will be a great help.

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

Thanks for answer. I already created date table:

I added "+0" and the measure is calculated properly but it still need to be changed because on chart we see dates (start of the week) where we do not have any tasks (in time record table - it is fact table):

Tasks occures in third quarter:

Chart:

Start of Week = Dates[date_date]-WEEKDAY(Dates[date_date];2) + 1

%_Time_avg_per_case_in_hours =

DIVIDE ([Time_avg_per_case_in_hours]; (

CALCULATE([Time_avg_per_case_in_hours]; FILTER(ALL(tasks); tasks[task productivity] = "Productive")) +

CALCULATE([Time_avg_per_case_in_hours]; FILTER(ALL(tasks); tasks[task productivity] = "Unproductive")));0) +0

[v-lili6-msft]

hi @Anonymous 

If possible, could you share us a simple sample pbix file for us have a test?

Regards,

Lin

[Anonymous · Accepted solution]

The solution is below:

%_Time_avg_per_case_in_hours =

DIVIDE (    

        IF(

            (CALCULATE([Time_avg_per_case_in_hours]; FILTER(ALL(tasks); tasks[task productivity] = "Productive")) = BLANK() &&

            CALCULATE([Time_avg_per_case_in_hours]; FILTER(ALL(tasks); tasks[task productivity] = "Unproductive")) <> BLANK ())

            ||          

            (CALCULATE([Time_avg_per_case_in_hours]; FILTER(ALL(tasks); tasks[task productivity] = "Productive"))<> BLANK() &&

            CALCULATE([Time_avg_per_case_in_hours]; FILTER(ALL(tasks); tasks[task productivity] = "Unproductive")) = BLANK ());         

            [Time_avg_per_case_in_hours] + 0;           

            [Time_avg_per_case_in_hours]);

            (CALCULATE([Time_avg_per_case_in_hours]; FILTER(ALL(tasks); tasks[task productivity] = "Productive")) + CALCULATE([Time_avg_per_case_in_hours]; FILTER(ALL(tasks); tasks[task productivity] = "Unproductive")))

        ;0)