Show dates with no data on chart

[huguestremblay]

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.

[MFelix · Accepted solution]

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.

[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]

Hi @huguestremblay ,

I was looking at your information, on the top table and your chart is not returning the correct values if you look at the second table where you have the filter with the selection of template B you have data in March, June and November however your chart only present March and november, don't know if it's because in June the value of colour is blank but what is the correct value?

Try to use the following measure:

CountID = 
VAR temptable =
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[ID],
            'Table'[Date],
            'Table'[Template],
            'Table'[Color],
            "@Filtering", [Filter]
        ),
        [@Filtering] = 1
    )
RETURN
    COUNTROWS ( temptable )

Not chanigng the filter logic because I don't really understand what is the purpose there are so many nested IF that I get lost, sorry if you can explain better, maybe with an example of what you want to filter out and how the slicer work together I can check it.

[huguestremblay]

Hello @MFelix, thanks again for your help.  I realized that my measure wasn't 100% accurate and needs to be modified for some of the scenarios.  Basically, what I need is to have the interaction between the 2 slicers be "OR" such that whatever value(s) the users select in either slicer, the result would include all records that meet any of their selection of color or template.  If nothing is selected in either slicer, then all record would show.

Regards,

Hugues.

[MFelix]

Hi @huguestremblay ,

Sorry for asking more question to what I can understand you want to show all values selected in both slicers meaning if you select color blue and template B you will get all dsta that has blue B or blue and B is this correct?

[huguestremblay]

@MFelix yes this is correct.  I have fixed my measure and it now behaves exactly as expected.

Filter =

//Blank Color and Template

IF ( AND( ISFILTERED('Color'[Color])=FALSE, ISFILTERED('Template'[Template])=FALSE), DISTINCTCOUNT([ID]),

//Blank Color, Value in Template

IF ( AND( ISFILTERED('Color'[Color])=FALSE, ISFILTERED('Template'[Template])=TRUE),

IF (MAX('Table'[Template])="",0,

IF (

SEARCH ( MAX('Table'[Template]), CONCATENATEX ( 'Template', [Template], "," ), DISTINCTCOUNT([ID]), 0 ) > 0

,

DISTINCTCOUNT([ID]),

0

))

,

//Blank Template, Value in Color

IF ( AND( ISFILTERED('Color'[Color])=TRUE, ISFILTERED('Template'[Template])=FALSE),

IF (MAX('Table'[Color])="",0,

IF (

SEARCH ( MAX('Table'[Color]), CONCATENATEX ( 'Color', [Color], "," ), DISTINCTCOUNT([ID]), 0 ) > 0

,

DISTINCTCOUNT([ID]),

0

))

,

//Both filtered

//Blank Color

IF ( max('Table'[Color]) = "",

IF (MAX('Table'[Template])="",0,

IF (

SEARCH ( MAX('Table'[Template]), CONCATENATEX ( 'Template', [Template], "," ), DISTINCTCOUNT([ID]), 0 ) > 0

,

DISTINCTCOUNT([ID]),

0

)),

//Blank Template

IF ( max('Table'[Template]) = "",

IF (MAX('Table'[Color])="",0,

IF (

SEARCH ( MAX('Table'[Color]), CONCATENATEX ( 'Color', [Color], "," ), DISTINCTCOUNT([ID]), 0 ) > 0

,

DISTINCTCOUNT([ID]),

0

)),

//No Blank

IF (

OR ( SEARCH ( MAX('Table'[Color]), CONCATENATEX ( 'Color', [Color], "," ), DISTINCTCOUNT([ID]), 0 ) > 0, SEARCH ( MAX('Table'[Template]), CONCATENATEX ( 'Template', [Template], "," ), DISTINCTCOUNT( [ID]), 0 ) > 0),

DISTINCTCOUNT([ID]),

0

)))

)))

What I use your additional CountID Measure, the chart also works as expected.

My issue now is that it work in my basic Sample Report, but my actual report has a lot of columns and I don't think that the CountID measure will handle all those columns...

Regards,

Hugues.

[MFelix]

Hi @huguestremblay ,

You can use this measure for any of your calculations, it should work no matter what is the column.

[huguestremblay]

Looks like using 

CountID = COUNTROWS(FILTER('Table','Table'[Filter]<>0))

also works as expected and is simpler.

[MFelix]

Hi @huguestremblay

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. 

[huguestremblay]

Hello @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.