Forum Discussion
Helper IIShow dates with no data on chart
Hello,
I have read through countless posts and tried everithing I could think of but cannot get what I want.
I have a bar chart that shows data for the last 12 months, with the date on the x-axis. I want the blank months to appear on the chart but cannot get it to work.
The report uses a measure that make my 2 slicers work with an "OR" logic, and for some reason, this seems to interfere with my ability to show all months on the axis (it used to work before I added those slicers).
In the image below, the Date comes from a Date table that contains each month of 2020, but only the "empty" months between those that contain data are showing (I would also want to see Jan, Feb and Dec on the axis).
The sample report that I created is available here: https://www.dropbox.com/s/m0nz2c0oz5ei24z/SampleReport.pbix?dl=0
Any help would be greatly appreciated.
Hugues.
Hi huguestremblay ,
You issue with this is the FILTER measure that is giving you additional context to your measure and not returning the values for the months that are not selected.
I have made the following measure based on your FILTER just changed 1 by COUNT ID:
Filter__ = IF ( ISFILTERED ( 'Color'[Color] ) = FALSE; IF ( ISFILTERED ( 'Template'[Template] ) = FALSE (); 1; IF ( MAX ( 'Table'[Template] ) = ""; 0; IF ( SEARCH ( MAX ( 'Table'[Template] ); CONCATENATEX ( 'Template'; [Template]; "," ); COUNT('Table'[ID]); 0 ) > 0; COUNT('Table'[ID]); 0 ) ) ); IF ( SELECTEDVALUE ( 'Table'[Template] ) = BLANK (); IF ( MAX ( 'Table'[Color] ) = ""; 0; IF ( SEARCH ( MAX ( 'Table'[Color] ); CONCATENATEX ( 'Color'; [Color]; "," ); 1; 0 ) > 0; COUNT('Table'[ID]); 0 ) ); IF ( MAX ( 'Table'[Color] ) = ""; 0; IF ( SEARCH ( MAX ( 'Table'[Color] ); CONCATENATEX ( 'Color'; [Color]; "," ); 1; 0 ) > 0; COUNT('Table'[ID]); IF ( MAX ( 'Table'[Template] ) = ""; 0; IF ( SEARCH ( MAX ( 'Table'[Template] ); CONCATENATEX ( 'Template'; [Template]; "," ); 1; 0 ) > 0; COUNT('Table'[ID]); 0 ) ) ) ) ) )Result is in attach PBIX file:
I'm not really sure what you want to achieve with the FILTER measure but having so many nested IF is confusing believe you would be better off with a SWITCH function if can please explain the purpose of the FILTER I can change the syntax to SWITCH.
10 Replies
- MFelix
Super User
Hi huguestremblay ,
You issue with this is the FILTER measure that is giving you additional context to your measure and not returning the values for the months that are not selected.
I have made the following measure based on your FILTER just changed 1 by COUNT ID:
Filter__ = IF ( ISFILTERED ( 'Color'[Color] ) = FALSE; IF ( ISFILTERED ( 'Template'[Template] ) = FALSE (); 1; IF ( MAX ( 'Table'[Template] ) = ""; 0; IF ( SEARCH ( MAX ( 'Table'[Template] ); CONCATENATEX ( 'Template'; [Template]; "," ); COUNT('Table'[ID]); 0 ) > 0; COUNT('Table'[ID]); 0 ) ) ); IF ( SELECTEDVALUE ( 'Table'[Template] ) = BLANK (); IF ( MAX ( 'Table'[Color] ) = ""; 0; IF ( SEARCH ( MAX ( 'Table'[Color] ); CONCATENATEX ( 'Color'; [Color]; "," ); 1; 0 ) > 0; COUNT('Table'[ID]); 0 ) ); IF ( MAX ( 'Table'[Color] ) = ""; 0; IF ( SEARCH ( MAX ( 'Table'[Color] ); CONCATENATEX ( 'Color'; [Color]; "," ); 1; 0 ) > 0; COUNT('Table'[ID]); IF ( MAX ( 'Table'[Template] ) = ""; 0; IF ( SEARCH ( MAX ( 'Table'[Template] ); CONCATENATEX ( 'Template'; [Template]; "," ); 1; 0 ) > 0; COUNT('Table'[ID]); 0 ) ) ) ) ) )Result is in attach PBIX file:
I'm not really sure what you want to achieve with the FILTER measure but having so many nested IF is confusing believe you would be better off with a SWITCH function if can please explain the purpose of the FILTER I can change the syntax to SWITCH.
huguestremblayHello MFelix , thank you for your help! My goal is to have both slicers work with an "or" logic if values are selected in both of them. So if no color or template is slected, all data will show. If only a color (or colors) is(are) selected, only those records will show, same for template. If values are selected in both slicers, I need all records that are of the selected color(s) or template(s) to show. I don't know much about DAX so I did not create that measure, it came from a post I found in the community... If you have a simpler solution, I am open to it.
Thans again,
Hugues.
- huguestremblay
I did some additional testing with your fix and identified situations where your chart is not behaving as expected.
1) So selection in either of the slicers - should show a total of 10 records with 7 blank months but shows a count of 1 for each of the 12 months.
2) Selection(s) in one of the slicers but not in the second - chart is inaccurate
Regards,
Hugues.
- MFelix
As refered I change your measure maybe some 1 need to be replace bit the count Id formula.
I will try to adjust it correctly now you explained the purpose of the filter.