Forum Discussion

Aho00's avatar
Aho00
Regular Visitor
8 months ago
Solved

Chart without data

Hello, I need some help

I would like to count the rows from my data table and show them in a chart. And I want it to display 0 when there is no data for the one no showing up in my data table. My data starts from September and I use a calendar table.


The problem is when I choose "to show items without data" , it cuts the chart and it also show the chart from january to december even with a slicer while september is selected which I don't want.

 

And also by adding +0 to countrows (and removing "show items without data"). The chart is fine but it also start from January to December.
I want the chart to show me only the month that I selected from my slicer but it shows me also all the other months.


What I want (in green):

 

Thanks for your help

 

Need Help
  • danextian's avatar
    danextian
    8 months ago

    Hi Aho00 

     

    Adding 0 forces DAX to add 0 even for the rows that are not supposed to be there or within the slicer selection. Add a condition do your measure that checks whether a specific date is within the selected range

    VAR _start =
    MINX ( ALLSELECTED ( CalendarTable ), CalendarTable[date] )
VAR _end =
    MAXX ( ALLSELECTED ( CalendarTable ), CalendarTable[date] )
RETURN
    IF (
        SELECTEDVALUE ( CalendarTable[date] ) >= _start
            && SELECTEDVALUE ( CalendarTable[date] ) <= _end,
        [your measure] + 0
    )

     

     

4 Replies

  • alish_b's avatar
    alish_b
    Super User
    8 months ago

    Hey Aho00 ,

     

    How many years of data do you have in your model? 

    I can replicate  something similar to your case only in case where the data model spans for more than one year, so basically if I have October selected, the line chart spans from Oct 2020 to Oct 2021 with all the months in between (if I have data of 2022 and 2021). Is this the same case for you and do you want to show only months Oct 2020 and Oct 2021 side by side? 
    Also, I am assuming that the slicer and the x-axis both come from the Calendar table.

    • Aho00's avatar
      Aho00
      Regular Visitor
      8 months ago

      Hello, I have a slicer for the month and one for the year. Technically, I just want to get a chart from a specific month from a specific year. And yes, I'm using the calendar table for the x-axis and for the slicer. But thanks for the answer, someone else gave me the answer and it works fine.

  • danextian's avatar
    danextian
    Super User
    8 months ago

    Hi Aho00 

     

    Adding 0 forces DAX to add 0 even for the rows that are not supposed to be there or within the slicer selection. Add a condition do your measure that checks whether a specific date is within the selected range

    VAR _start =
    MINX ( ALLSELECTED ( CalendarTable ), CalendarTable[date] )
VAR _end =
    MAXX ( ALLSELECTED ( CalendarTable ), CalendarTable[date] )
RETURN
    IF (
        SELECTEDVALUE ( CalendarTable[date] ) >= _start
            && SELECTEDVALUE ( CalendarTable[date] ) <= _end,
        [your measure] + 0
    )

     

     

    • Aho00's avatar
      Aho00
      Regular Visitor
      8 months ago

      Hello,


      Thanks for the answer, it works great!

      Have a nice day.