Forum Discussion

RemondV's avatar
RemondV
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's avatar
    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

    D

RemondV's avatar
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's avatar
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

D

  • RemondV's avatar
    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