Forum Discussion

Shpongle's avatar
Shpongle
Helper III
6 years ago
Solved

Probably an easy count Q

Hi guys,

 

If I spend over an hour trying to figure something out, I just post it here for the pros to figure out!

 

This seems relatively simple, and I have done this before on a different PBIX file but I can't recreate it even though I can reference the working file...

 

I am trying to create a stacked column chart that shows the number of tickets opened and closed by day. It should look like this:

I have two tables: One of them is my date table Date, and the other is my source data table Service.

 

There is a field in my date table called date, which is data type date/time.

There are two relationships from the date table to the  Service table:

  • 'date'[DateAsDate] -> 'Service'[date_closed]      (active relationship)
  • 'date'[DateAsDate] -> 'Service'[date_entered]    (inactive)

I created a couple of calculated columns in the Service table following the protocol that I used previously with success

  • Column Open = calculate(count(Service[SR_Service_RecID]),Service[date_entered])

  • Column Closed= calculate(count(Service[SR_Service_RecID]),Service[date_closed])

Then I plugged them into a stacked column chart like shown above but the chart remains blank!

 

Any ideas?

 

THANK YOU in advance 🙂

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Shpongle ,

     

    It was showing as Day no, because the earlier selection in the x Axis was Day (under the heirarchy of Date).

     

    Have changed the x axis values .Pulled the Date from the Calendar table and removed the hierarchy.

     

     

     

     

    Incase you want for each date, you can change it the values of X axis to Categorical.

     

     

     

    Hope this helps.

     

    Regards,

    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

     

12 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Shpongle add measure like this, not columns

     

    Measure Open = count(Service[SR_Service_RecID])

Measure Closed= calculate([Measure Open],Userelationship(Datetable[date], Service[date_closed]))

     

    and use the above measures in the visual

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    • Shpongle's avatar
      Shpongle
      Helper III
      6 years ago

      Thanks, parry2k - I agree that seems like it *should* work, but I got similar results: No data shows up and I observe that the left axis shows percentage!

      I figure I'll share all the details in case I screwed something up. 

       

      Here are the tables and relationships:

       

      Following is how I set up the measures:

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Shpongle change date_entered data type to date instead of date/time and that's the issue.

         

         

        I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!