Forum Discussion

SaCvP125's avatar
SaCvP125
Frequent Visitor
8 years ago
Solved

Create a Clustered Column Chart using Two Summarize Measures

Hi guys,

 

I'm trying to create the following query using DAX:
WITH OPEN_DATA
                        AS
                            (
                            SELECT COUNT(DISTINCT [Ticket ID]) AS [Num_Open_Tickets]
                                        ,CAST([OPEN DATE] AS DATE) AS [Open Date]
                            FROM TableA
                            WHERE [OPEN DATE] >= DATEADD(DAY, -5,GETDATE())
                            GROUP BY CAST([OPEN DATE] AS DATE)
                            ),
            CLOSE_DATA
                        AS
                            (
                            SELECT COUNT(DISTINCT [Ticket ID]) AS [Num_Close_Tickets]
                                        ,CAST([CLOSE DATE] AS DATE) AS [Close Date]
                         FROM TableB
                         WHERE [OPEN DATE] >= DATEADD(DAY, -5,GETDATE())
                         GROUP BY CAST([CLOSE DATE] AS DATE)
                            )

SELECT CAST(COALESCE([Open Date],[Close Date]) AS DATE) AS [Date]
            ,ISNULL([Num_Open_Tickets],0) AS [Num_Open_Tickets]
            ,ISNULL([Num_Close_Tickets],0) AS [Num_Close_Tickets]
FROM OPEN_DATA
FULL OUTER JOIN CLOSE_DATA ON
    OPEN_DATA.[Open Date] = CLOSE_DATA.[Close Date]

 

For that I'm using the followinge measures to calculate the [Num_Open_Tickets] and [Num_Close_Tickets]:

Num_Open_Tickets= CALCULATE(
DISTINCTCOUNT(Tickets_Volumes[Ticket ID]);
ALLEXCEPT(Tickets_Volumes;Tickets_Volumes[OPEN DATE]))

 

Num_Close_Tickets = CALCULATE(
DISTINCTCOUNT(Tickets_Volumes[Ticket ID]);
ALLEXCEPT(Tickets_Volumes;Tickets_Volumes[CLOSE DATE]))

 

In powerBI I'm using a Clustered Column Chart with [Open Date ] in Axis and the Measures calculated in Values. But I'm getting the following chart:

 But I'm trying to get this:

 

 

How Can I get this?

 

Many thanks!

 

 

  • SaCvP125,

     

    Add a calendar table and use the following measures.

    Num_Open_Tickets =
    VAR d =
        MAX ( 'Calendar'[Date] )
    RETURN
        CALCULATE (
            DISTINCTCOUNT ( Tickets_Volumes[Ticket ID] ),
            Tickets_Volumes[OPEN DATE] = d
        )
    
    Num_Close_Tickets =
    VAR d =
        MAX ( 'Calendar'[Date] )
    RETURN
        CALCULATE (
            DISTINCTCOUNT ( Tickets_Volumes[Ticket ID] ),
            Tickets_Volumes[CLOSE DATE] = d
        )
    

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support

    SaCvP125,

     

    Add a calendar table and use the following measures.

    Num_Open_Tickets =
    VAR d =
        MAX ( 'Calendar'[Date] )
    RETURN
        CALCULATE (
            DISTINCTCOUNT ( Tickets_Volumes[Ticket ID] ),
            Tickets_Volumes[OPEN DATE] = d
        )
    
    Num_Close_Tickets =
    VAR d =
        MAX ( 'Calendar'[Date] )
    RETURN
        CALCULATE (
            DISTINCTCOUNT ( Tickets_Volumes[Ticket ID] ),
            Tickets_Volumes[CLOSE DATE] = d
        )
    
    • Anonymous's avatar
      Anonymous
      Not applicable

      I think it won't work. When I filter by a specific month it will give me the number of projects open and closed in that month.

       

      I want to know: for the projects opened in January, how many closed in January, February, March, etc.