Forum Discussion

Frequent Visitor

6 years ago

Solved

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 da...

Anonymous
6 years ago
File attached.

Best

D
Invoices by Processing Step.pbix328 KB

Anonymous

6 years ago

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

Anonymous

Not applicable

6 years ago

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

RemondV

Frequent Visitor

6 years ago

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

6 years ago

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

RemondV
Frequent Visitor
6 years ago
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

Forum Discussion

Group over dates and count max value

Related content

Unable to group date column for count or matrix

Count of groups of distinct count by group

Counting nulls

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience