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 ta...
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

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.