Line Chart question

[shuiting]

Fairly new to Power BI and I am having an issue that I can't seem to resolve. I don't know if I'm asking this in the right way, but I have a line chart with two lines of data on it. X-axis is made up of dates and y-axis is made up of the count of activities (calls or emails). Currently, it doesn't display dates where there are 0 calls and 0 emails (This is what I want). However, there is a break in calls due to a blank value, but there is 1 email for that day. How do I show a continuous line for each category if one of the categories has a count that is greater than 0?

[shuiting · Accepted solution]

After a lot of trial and error, I figured out the solution. 

Measure = IF(

   CALCULATE(

      COUNTA(table.column,table.column IN { "value A" })>0

 ||

   CALCULATE(

      COUNTA(table.column),table.column IN { "value B" })>0,

   COUNTA('YTD (2)'[Call/Email])+0,

   COUNTA('YTD (2)'[Call/Email])

)

you can copy and paste the CALCULATE function along with addition || operators to account for more values. Side note, || is equal to OR.

[v-yulgu-msft]

Hi @shuiting,

You could create a relationship between fact data table and a date dimention table based on [dates] field. Add [dates] from date dimention table onto X-axis of line chart, and set its type to "continuous".

If you still have any question, please post sample data. How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[shuiting]

@v-yulgu-msft,

Thanks for the reply!

When I try to set the x-axis to continuous it just goes right back to categorical. The data is from a csv file and essentially it looks like this:

Date	Activity Type
9/7/2018	Email
9/7/2018	Email
9/7/2018	Connect
9/7/2018	Connect
9/7/2018	No Connect
9/7/2018	No Connect
9/8/2018	Email
9/9/2018	Connect
9/10/2018	No Connect
9/12/2018	Connect
9/12/2018	Email
9/12/2018	No Connect

When I go to plot the count of each activity type on the y-axis and the date on the x-axis I end up with broken lines. For example, on 9/7/2018 there are 2 connects and on 9/9/2018 there is one connect. I want to count the number of activities for each day where there is at least one type of activity. So, 9/7/2018 should show as 2 Email, 2 Connect, and 2 No Connect; 9/8/2018 should show as 1 Email, 0 Connect, and 0 No Connect; 9/9/2018 should show as 0 Email, 1 Connect, 0 No Connect; 9/10/2018 should show as 0 Email, 0 Connect, and 1 No Connect; 9/11/2018 should not show up at all since there were no activities; 9/12/2018 should show as 1 Email, 1 Connect, and 1 No Connect.

I can add +0 to the end of the count function, but then that makes the days where there was no activity show up on the chart and I don't want the days with 0 total activities to show up on the chart.