Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm having an issue with a "line & stacked column chart" in which I'm showing, for the last 12 months, the number of complaints and the complaints per MT. As long as there are complaints for a specific month in the dataset, the visual is showing all the months. However, when there's no complaint for a month (hence also no complaint per MT, the graph is simply skipping that month. Is there a way to show the months with no values?
Sample which is working when there's a complaint for all months:
Below the sample when a site is selected which does not have complaints for every month in the last 12 months.
What I need is for this graph to show the "missing" months , basically as a flat column and line
This is the DAX I'm using to calculate the last 12 months actuals, using a disconnected date table:
I've tried adjusting the part after RETURN to the following, but then the visual is showing ALL months again which is what I don't want:
Please any luck with this. @Bthurlings Please can you send solution here or if I can send a dm. i need it for the project I am currently working on
Hi,
please can you share any sample data set, it will be easy to trace the issue.
Thanks,
Surya
Click on the chart
go to the fields card on the right
find the item on the axis (month)
click the drop down arrow (or is it right click, or is it an ellipsis?)
select “show items with no values”
@Bthurlings wrote:Hi,
I'm having an issue with a "line & stacked column chart" in which I'm showing, for the last 12 months, the number of complaints and the complaints per MT. As long as there are complaints for a specific month in the dataset, the visual is showing all the months. However, when there's no complaint for a month (hence also no complaint per MT, the graph is simply skipping that month. Is there a way to show the months with no values?
Sample which is working when there's a complaint for all months:
Below the sample when a site is selected which does not have complaints for every month in the last 12 months.
What I need is for this graph to show the "missing" months , basically as a flat column and line
This is the DAX I'm using to calculate the last 12 months actuals, using a disconnected date table:
Complaints Last 12 months =VAR CurrentDate = MAX ( 'Calendar'[Date] )VAR PreviousDate = EDATE( CurrentDate ; -12 )VAR Result =CALCULATE( [Total Complaints] ;FILTER( DimDateTable ; DimDateTable[Date] >= PreviousDate && DimDateTable[Date] <= CurrentDate ))RETURNResult
I've tried adjusting the part after RETURN to the following, but then the visual is showing ALL months again which is what I don't want:
RETURNCALCULATE( IF( Result = BLANK() ; 0 ; Result) )Any help on how to fix is highly appreciated.
Thanks, but I already tried this. Doing so will also display ALL months instead of just the last 12 months...
Hello Bthurlings,
Did you ever resolve this issue?
I have the same issue and was wondering how you did.
Thanks
Yes indeed I managed to resolve. I used the initial measure
IF ( ISBLANK ( Result ) , 0 , Result )
and combined that with logic applied to the date table (a column which indicates if a date is 12 months in the past) which I use as a filter on a viz or report page. Not sure if it's the most optimal but it's flexible.
Hello Bthurlings,
Will you be so kind to give a step by step?
I created the measure IF ( ISBLANK ( Result ) , 0 , Result ) like you said and put the Calender[Date] filed as the X-axis and also put the Calender]Date] as the Filter in the Filter all pages filter panel and filtered by last 6 Calender months.
That did is still showing the other months as well.
What is the logic that you cobined with the Date table that you mentioned?
Thanks
PM me and I'll share a sample pbix with you (I can't seem to find the option to publish that here)
Hey bthurlings,
I just sent you a Private Message
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |