Forum Discussion
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!
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-msftCommunity Support
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 )- AnonymousNot 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.