Forum Discussion
Line Chart question
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?
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.
3 Replies
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
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.
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.
