Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Matrix Column disappears when there is no data in selected date range

Hello!

I'm new here so I apologize if this has been asked & answered before. I did a search and still could'nt resolve it. Saw some solutions but they did not apply for the Date data type 

 

I have  a matrix with several columns showing number of occurences per month.

For analysis, I use a slider and select a date range say (last 12 months) and I see all the columns.

When I adjust the date range to last 8 months, I need to still see all columns (including blank ones)

However Power Bi hides the blank column. 

I have tried ticking the show items with no dat box - no success .

I also tried creating a column with = IF(ISBLANK('MyTable'[Column_with_DateCount]),0,''MyTable'[Column_with_DateCount])

This did not work for me either.

I have uploaded a document showing what I'm seeing and what I would like to see. Let me know if I need to clarify anything and thanks in advance.

 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    To clarify,

    What I would like to see is the table with all the columns irrespective of if they are blank or not.

    Sorry for the confusion.

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    You need to create a new measure and a calendar table. The following is my sample you can reference to modify yours.

     

    1. Create a calendar table. (Note: there is no relationship between the two tables.)
    Table = CALENDARAUTO()

     

    1. Create a measure and check the option of show items with no data
    Measure = CALCULATE(SUM(financials3[ Sales]),FILTER(financials3,'financials3'[Date] in VALUES('Table'[Date])))

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-xuding-msft ,

      Thank you for your help. Unfortunately it has not worked.

      I was able to create the calendar table successfully, however after creating the measure I get the following error:

      "The function SUM cannot work with values of type String"

       

      I suspect it is because the numbers in my table are counts of events(i.e how many times a site visit was made & Data Type = Date/Time), not values like sales as used in your example.

       

      Not sure if this information helps or confuses things but I'm really greatful for your help so far. 

       

      Would the measure still work if we used somethig else instead of SUM?

       

      Thanks