Forum Discussion

huguestremblay's avatar
huguestremblay
Icon for Helper II rankHelper II
5 years ago
Solved

Show 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...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

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's avatar
huguestremblay
Icon for Helper II rankHelper II
5 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    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. 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      huguestremblay
      Icon for Helper II rankHelper II
      5 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        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?