Forum Discussion
Group over dates and count max value
- 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
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
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