cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaCvP125 Regular Visitor
Regular Visitor

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:
Error1.PNG

 But I'm trying to get this:Error2.PNG

 

 

How Can I get this?

 

Many thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create a Clustered Column Chart using Two Summarize Measures

@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
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Create a Clustered Column Chart using Two Summarize Measures

@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
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.