DAX filter issue

RemondV — Tue, 14 Apr 2020 15:12:52 GMT

I am a newbe in DAX and i have a issue in figuring out how to correctly filter my data. This is the issue. I have a set of invoices that go through a set of steps during processing. in a day they can go through multiple steps. I am trying to figure out how many distinct invoices at a given day (selected by a user) i have in that day. I am using SSAS in direct query mode.

I can figure this out perfectly in SQL but in DAX i have not been able to figure this out.

This is the data set i use for testing

InvoiceNumber	ProcessStep	StartDate	EndDate	TimeOrder
200	Step 1	2020-03-30	2020-04-01	1
200	Step 2	2020-04-01	2020-04-01	2
200	Step 3	2020-04-01	2020-04-06	3
200	Step 4	2020-04-07	NULL	4
300	Step 1	2020-04-01	2020-04-01	1
300	Step 2	2020-04-01	2020-04-04	2
300	Step 3	2020-04-04	2020-04-06	3
300	Step 4	2020-04-07	2020-04-09	4
300	Step 3	2020-04-10	NULL	5

these are the results i get from my queries

InvoiceNumber	ProcessStep	StartDate	EndDate	TimeOrder
200	Step 3	2020-04-01	2020-04-06	3
300	Step 3	2020-04-04	2020-04-06	3

ProcessStep	total
Step 3	2

Below i have created a small sample script for SQL to show how i do this in sql.

CREATE TABLE [dbo].[InvoiceProcess](

[InvoiceNumber] [int] NOT NULL,

[ProcessStep] [nvarchar](50) NOT NULL,

[StartDate] [date] NOT NULL,

[EndDate] [date] NULL,

[TimeOrder] [int] NOT NULL

) ON [PRIMARY]

GO;

TRUNCATE TABLE [dbo].[InvoiceProcess];

GO;

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (200, N'Step 1', CAST(N'2020-03-30' AS Date), CAST(N'2020-04-01' AS Date), 1)

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (200, N'Step 2', CAST(N'2020-04-01' AS Date), CAST(N'2020-04-01' AS Date), 2)

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (200, N'Step 3', CAST(N'2020-04-01' AS Date), CAST(N'2020-04-06' AS Date), 3)

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (200, N'Step 4', CAST(N'2020-04-07' AS Date), NULL, 4)

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 1', CAST(N'2020-04-01' AS Date), CAST(N'2020-04-01' AS Date), 1)

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 2', CAST(N'2020-04-01' AS Date), CAST(N'2020-04-04' AS Date), 2)

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 3', CAST(N'2020-04-04' AS Date), CAST(N'2020-04-06' AS Date), 3)

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 4', CAST(N'2020-04-07' AS Date), CAST(N'2020-04-09' AS Date), 4)

INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 3', CAST(N'2020-04-10' AS Date), NULL, 5)

GO;

DECLARE @DATE DATE = '2020-04-04';

WITH LastInvoice

AS (

SELECT InvoiceNumber,max(TimeOrder) TimeOrder

FROM InvoiceProcess

WHERE @DATE between StartDate and EndDate

GROUP BY InvoiceNumber

)

SELECT IP.InvoiceNumber

, IP.ProcessStep

, IP.StartDate

, IP.EndDate

, IP.TimeOrder

FROM InvoiceProcess IP

INNER JOIN LastInvoice LI on IP.InvoiceNumber = LI.InvoiceNumber

and IP.TimeOrder = LI.TimeOrder

ORDER BY

IP.ProcessStep

,IP.StartDate;

WITH LastInvoice

AS (

SELECT InvoiceNumber,max(TimeOrder) TimeOrder

FROM InvoiceProcess

WHERE @DATE between StartDate and EndDate

GROUP BY InvoiceNumber

)

SELECT

IP.ProcessStep

, COUNT(DISTINCT IP.InvoiceNumber)

FROM InvoiceProcess IP

INNER JOIN LastInvoice LI ON IP.InvoiceNumber = LI.InvoiceNumber

AND IP.TimeOrder = LI.TimeOrder

GROUP BY IP.ProcessStep

ORDER BY

IP.ProcessStep;

I would be very much obliged if someone could help me figure this out in DAX?

Kind regards

Remond

Re: DAX filter issue

Anonymous — Wed, 15 Apr 2020 12:09:49 GMT

SSAS in DQ mode? You probably mean Live Connection mode.

Never mind. If you can create measures, then the measure is

DISTINCTCOUNT( InvoiceProcess[InvoiceNumber] )

Best
D

Re: DAX filter issue

RemondV — Thu, 30 Apr 2020 06:52:30 GMT

Hi DarLove

Thx for the reply but this is not the solution and does not address the real issue. I have created a new issue as i realised my description did not make clear my problem.

Here is the new topic i created

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Group-over-dates-and-count-max-value/m-p/1058299#M14636

Regards

Remond

topic Re: DAX filter issue in DAX Commands and Tips

DAX filter issue

Re: DAX filter issue

Re: DAX filter issue