Forum Discussion

gemcityzach's avatar
gemcityzach
Helper IV
2 years ago
Solved

Stacked Bar or Line Chart Using Multiple Data from Same Table along with a Date/Calendar Table

Hello,

 

I'm trying to plot various types of time intelligence metrics on the same chart. I have a single table, that is a SharePoint list, that has four date fields. I also have a Calendar/Date table and I have created relationships to these four date fields. Created Date is primary/active and the rest are inactive.

 

I'd like to be able to plot Created (count of created) and Closed (count of closed) in a stackbar chart or even a line chart of counts of labels by Created and Closed. I know I need to use the USERELATIONSHIP function so I can activate Created or Closed in a Measure to count/plot relative to the Calendar/Date Table. But I'm not sure the best way to do it.

IDLabelIdentifiedCreatedDecisionClosed
1Shoes3/1/20243/1/20243/31/20243/31/2024
2Guitars3/2/20243/2/20243/31/20243/31/2024
3Shoes4/2/20244/2/20244/5/20244/5/2024
4Balloons4/22/20244/22/20244/23/20244/23/2024
5Cheese4/5/20244/22/20244/23/20244/23/2024
Need Help
  • Alex87's avatar
    Alex87
    2 years ago
    Count Closed =
    CALCULATE(
        COUNT(Data[Closed]),
        USERELATIONSHIP(Dates[Date], Data[Closed]))
     
    Count Created =
    CALCULATE(
        COUNT(Data[Created]),
        USERELATIONSHIP(Dates[Date], Data[Created]))
     
    As you have an active relationship on Date created you can use directly as a DAX Measure = 
    COUNT(Data[Created]) , but I like to keep the consistency between the formula patterns. It will work either way.
     
    I slightly changed your sample data as the count was the same between the two

     

    If it answers your query please mark my post as a solution

3 Replies

  • Alex87's avatar
    Alex87
    Solution Sage
    2 years ago
    Count Closed =
    CALCULATE(
        COUNT(Data[Closed]),
        USERELATIONSHIP(Dates[Date], Data[Closed]))
     
    Count Created =
    CALCULATE(
        COUNT(Data[Created]),
        USERELATIONSHIP(Dates[Date], Data[Created]))
     
    As you have an active relationship on Date created you can use directly as a DAX Measure = 
    COUNT(Data[Created]) , but I like to keep the consistency between the formula patterns. It will work either way.
     
    I slightly changed your sample data as the count was the same between the two

     

    If it answers your query please mark my post as a solution

    • gemcityzach's avatar
      gemcityzach
      Helper IV
      2 years ago

      Is there a way to ensure that it's only pulling back fields with an actual closed date populated? My source is a SharePoint list and empty closed dates are populated with 'null' in PowerQuery. I tried adding a filter [Closed_Date] <> "" and also [Closed_Date] <> null, and other variations but it fails.

      • Alex87's avatar
        Alex87
        Solution Sage
        2 years ago

        It is a weird behavior. You can try different ways to filter out data

        if this does not work:

        Table.SelectRows(#"Removed Columns", each [Closed] <> null and [Closed] <> "")

        try filter data after a certain date:

        Table.SelectRows(#"Filtered Rows", each [Closed] > #date(2020, 1, 1))

        or maybe you can duplicate the column and transform the datatype to whole number and filter out the nulls or values above a certain amount.