Forum Discussion
RemondV
6 years agoFrequent 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 da...
- Anonymous6 years ago
File attached.
Best
D
- Anonymous6 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 __invoiceCountBest
D
Anonymous
6 years agoNot applicable
File attached.
Best
D