Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RemondV
Frequent Visitor

DAX filter issue

Hi 

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

 

2 REPLIES 2
Anonymous
Not applicable

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

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/1058...

 

 

Regards

Remond

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.