Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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 )
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 )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |