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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RemondV
Frequent Visitor

Group over dates and count max value

Hi 

I am a newbe in DAX and i have an issue that i have been breaking my head over for a while.

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. It can even go back and forth in steps during the day. I am trying to figure out how many distinct invoices at a given day (selected by a user) i have in at the end of that day grouped by step

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

30/03/2020

01/04/2020

1

200

Step 2

01/04/2020

01/04/2020

2

200

Step 3

01/04/2020

07/04/2020

3

200

Step 4

07/04/2020

NULL

4

300

Step 1

01/04/2020

01/04/2020

1

300

Step 2

01/04/2020

04/04/2020

2

300

Step 3

04/04/2020

07/04/2020

3

300

Step 4

07/04/2020

09/04/2020

4

300

Step 3

10/04/2020

NULL

5

400

Step 1

01/04/2020

01/04/2020

1

400

Step 2

01/04/2020

04/04/2020

2

400

Step 3

04/04/2020

04/04/2020

3

400

Step 4

04/04/2020

09/04/2020

4

400

Step 3

10/04/2020

NULL

5

 

This is what  iwould expect to be the outcome if i select the results for  2020-04-04

InvoiceNumber

ProcessStep

StartDate

EndDate

TimeOrder

200

Step 3

01/04/2020

07/04/2020

3

300

Step 3

04/04/2020

07/04/2020

3

400

Step 4

04/04/2020

09/04/2020

4

 

This is what  i expect to see for my final end result

ProcessStep

Count

Step 3

2

Step 4

1

I have tried the following dax But this does not quit do it 

 Measure =

VAR MyDate = LASTDATE(Dates[Date])

VAR MaxRow =

   ADDCOLUMNS (

       SUMMARIZE (

           CALCULATETABLE (

               InvoiceProcess,

               FILTER (

                   InvoiceProcess,

                   MyDate >= InvoiceProcess[StartDate]

                       && MyDate <= InvoiceProcess[EndDate]

               )

           ),

           InvoiceProcess[InvoiceNumber]

       ),

       "MaxOrder", CALCULATE ( MAX ( InvoiceProcess[TimeOrder] ) )

   )

RETURN

COUNTROWS(MaxRow)

 

 

 

Is there someone who can help me figure this out in DAX?

 

Kind regards

Remond

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

File attached.

 

Best

D

View solution in original post

Anonymous
Not applicable

Yeah... You're almost there. I've given you the full description:

# Invoices =
// Get the latest date visible in
// the current context from a DISCONNECTED
// table of dates.
VAR __selectedDate = MAX ( Dates[Day] )
// Harvest the currently visible steps from
// a DISCONNECTED slicer.
VAR __selectedProcessingSteps =
	VALUES ( Step[Processing Step] )
VAR __invoices =
    // Get a table of (unique) invoice numbers that
    // adhere to the conditions of the task.
    CALCULATETABLE (

    	FILTER (
            // In the current context, get the unique invoice numbers.
            VALUES ( 'Invoice Processing'[InvoiceNumber] ),
            // For each invoice number returned by VALUES,
            // find the max TimeOrder and get the corresponding
            // processing step. There will be only one due to
            // the uniqueness of the combination (InvNo, TimeOrder),
            // so VALUES (in __step) will return only one value that will then
            // be automatically converted by DAX into a scalar.
            VAR __maxTimeOrder =
            	// Calculate is needed here to perform context transition.
                CALCULATE ( MAX ( 'Invoice Processing'[TimeOrder] ) )
            VAR __step =
                // For the above max TimeOrder get the corresponding
                // process step. Also, this works correctly due to
                // context transition. As I said above, the outcome
                // will be a single item.
                CALCULATE (
                    VALUES ( 'Invoice Processing'[ProcessStep] ),
                    'Invoice Processing'[TimeOrder] = __maxTimeOrder
                )
            RETURN
                // For the invoice being iterated, check if
                // the latest step is in the selected steps.
                // This is because there can be many steps
                // visible - think: the total in a table/matrix.
                __step
                    IN __selectedProcessingSteps
        ),
        
        // Filter the invoice processing data for the records where
        // the selected date falls between the start and end date;
        // this will be applied first for CALCULATETABLE so that
        // FILTER can "feel" it and only see the relevant rows.
        // Pay attention to the type of the inequalities as it's
        // crucial to get the correct numbers.
        'Invoice Processing'[StartDate] <= __selectedDate,
        (
            __selectedDate <= 'Invoice Processing'[EndDate]
                || ISBLANK ( 'Invoice Processing'[EndDate] )
        )
    )
VAR __invoiceCount =
    COUNTROWS ( __invoices )
RETURN
    __invoiceCount

 

Best

D

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

File attached.

 

Best

D

HotChilli
Super User
Super User

Welcome to the forum.

Using a disconnected date table in a slicer to select the date.

Create a measure:

InvStage = VAR _date = SELECTEDVALUE(Dates[Date])
RETURN
CALCULATE(COUNT(InvoiceProcess[InvoiceNumber]) , 
    FILTER(InvoiceProcess, InvoiceProcess[StartDate] <= _date &&
                          (InvoiceProcess[EndDate] > _date || ISBLANK(InvoiceProcess[EndDate]))))

Drag the fields for the first outcome table on to the report canvas, and drag the measure on as well.

Select the date in the slicer.  That should show the table correctly.

 

Create another measure:

InvCount = COUNTX(InvoiceProcess, [InvStage])

Drag ProcessStep on to another table visual.  Drag the InvCount measure on.

This should give the 2nd table requested.

 

I've tested with the data provided.  Please see how you get on with a larger dataset

 

 

 

Anonymous
Not applicable

Hi @RemondV 

 

The measures by @HotChilli are incorrect. If only for the very condition under the first CALCULATE. So, be careful.

The measure should return 3 for Step 4 for 09/04/2020 but his measure returns 1.

Please see the correct calculation in the attached file in my previous post.

Best
D

Hi Darlove,

Thanks for takiing another look at my issue. This is a great help. And i agree at first glance the nunbers from @HotChilli seem to add up. But looking at it more closer i understand what you mean.

 

Thanks for the great sample you provided. I have been studing it and i think i understand what you do. I have put some comments in the way you calculate the measure to educate myself on my understanding of DAX. If you have time let me know if this is correct. 

 

# Invoices =
-- Find the selected date in the slicer
var __selectedDate = MAX( Dates[Day] )
--Create a table that holds the unique processing steps
var __selectedProcessingSteps = VALUES( Step[Processing Step] )
var __invoices =
--Create a table of invoices that have the max timeorder so you can itterate it to calculate the number of invoices
    CALCULATETABLE(
        FILTER(
--Create a table that holds the unique invoice numbers
            VALUES( 'Invoice Processing'[InvoiceNumber] ),
            --Create a table that holds the max timeorder value in the current filter context
var __maxTimeOrder = CALCULATE( MAX( 'Invoice Processing'[TimeOrder] ) )
            var __step =
--find the processing steps that have a timeorder values that equals the max timeorder in the current filter context
                CALCULATE(
                    VALUES( 'Invoice Processing'[ProcessStep] ),
                    'Invoice Processing'[TimeOrder] = __maxTimeOrder
                )
            return
--Return the step names that have teh max timeorder
                __step IN __selectedProcessingSteps
        ),
--filter the invoice processing data for the records where the selected date falls between start and enddate
-- this will be applied first for the calculatetable
        'Invoice Processing'[StartDate] <= __selectedDate,
        (
            __selectedDate <= 'Invoice Processing'[EndDate]
            ||
            ISBLANK( 'Invoice Processing'[EndDate] )
        )
    )
var __invoiceCount = COUNTROWS( __invoices )
return
    __invoiceCount
 
Kind regards
Remond
Anonymous
Not applicable

Yeah... You're almost there. I've given you the full description:

# Invoices =
// Get the latest date visible in
// the current context from a DISCONNECTED
// table of dates.
VAR __selectedDate = MAX ( Dates[Day] )
// Harvest the currently visible steps from
// a DISCONNECTED slicer.
VAR __selectedProcessingSteps =
	VALUES ( Step[Processing Step] )
VAR __invoices =
    // Get a table of (unique) invoice numbers that
    // adhere to the conditions of the task.
    CALCULATETABLE (

    	FILTER (
            // In the current context, get the unique invoice numbers.
            VALUES ( 'Invoice Processing'[InvoiceNumber] ),
            // For each invoice number returned by VALUES,
            // find the max TimeOrder and get the corresponding
            // processing step. There will be only one due to
            // the uniqueness of the combination (InvNo, TimeOrder),
            // so VALUES (in __step) will return only one value that will then
            // be automatically converted by DAX into a scalar.
            VAR __maxTimeOrder =
            	// Calculate is needed here to perform context transition.
                CALCULATE ( MAX ( 'Invoice Processing'[TimeOrder] ) )
            VAR __step =
                // For the above max TimeOrder get the corresponding
                // process step. Also, this works correctly due to
                // context transition. As I said above, the outcome
                // will be a single item.
                CALCULATE (
                    VALUES ( 'Invoice Processing'[ProcessStep] ),
                    'Invoice Processing'[TimeOrder] = __maxTimeOrder
                )
            RETURN
                // For the invoice being iterated, check if
                // the latest step is in the selected steps.
                // This is because there can be many steps
                // visible - think: the total in a table/matrix.
                __step
                    IN __selectedProcessingSteps
        ),
        
        // Filter the invoice processing data for the records where
        // the selected date falls between the start and end date;
        // this will be applied first for CALCULATETABLE so that
        // FILTER can "feel" it and only see the relevant rows.
        // Pay attention to the type of the inequalities as it's
        // crucial to get the correct numbers.
        'Invoice Processing'[StartDate] <= __selectedDate,
        (
            __selectedDate <= 'Invoice Processing'[EndDate]
                || ISBLANK ( 'Invoice Processing'[EndDate] )
        )
    )
VAR __invoiceCount =
    COUNTROWS ( __invoices )
RETURN
    __invoiceCount

 

Best

D

Hi Darlove,

 

Thank you  for taking the time to help me understand this. This is a great help for me in understand how DAX works. But also the way you need to think about how to shape your results. 

 

Kind Regards

Remond

Hi HotChili

Thx for the quick response. This solved my issue!.

 

But i was wondering looking at the folrmula and the relative simplicity how this could work.  I am trying to understand why it this formula will implicitly select the highest step without any lookup for the max value.  If you look at the value for invoicnumber 400 on the 4th of april i would expect the filter to retrieve 3 rows and it sort of baffles me that it still knows that "step 4" is the value i am looking for without and calcation ofr the max value.

 

If you have time to explain this to me it would be very helpfull in getting a better understanding of DAX.

 

In any way many thanks up to now for helping me!

 

 

Kind rgards

 

Remond

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors