Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

alternative to summarize

Hi Community,

I have a matrix table that I want to be filtered by Year and Month slicer. To do this, Im using 2 date dimension tables; one is linked to the model, and the other one is disconnected.

So, in the slicer I have Year/Month of the linked calendar, and in the columns of the matrix table the month of the disconnected calendar.

The result that I want to achieve is just a simple matrix table that when I select one month in the slicer, the table shows data from 01/07 (first day of the Fiscal Year) to the max date of the slicer selection. And in the Total column the sum of the selected months. 

To do this I am using Summarize, but I have a lot of formulas using the same pattern, and I think the performance of the data set is worsening.  Does it make sense or do you have any alternative to achieve the same result?

 

 

Monthly_Production =
VAR maxDateSelection =
    MAXX ( ALLSELECTED ( 'Calendar' ), MAX ( 'Calendar'[Fiscal MonthNumber] ) )
RETURN
    SUMX (
        SUMMARIZE (
            VALUES ( CalendarUnrel[Fiscal Month] ),
            CalendarUnrel[Fiscal Month],
            "ABCD",
                IF (
                    MAX ( 'CalendarUnrel'[Fiscal MonthNumber] ) <= maxDateSelection,
                    CALCULATE (
                        [Production],
                        FILTER (
                            ALL ( 'Calendar' ),
                            'Calendar'[Fiscal MonthNumber] = MAX ( 'CalendarUnrel'[Fiscal MonthNumber] )
                                && 'Calendar'[Fiscal Year] = MAX ( 'Calendar'[Fiscal Year] )
                        )
                    )
                )
        ),
        [ABCD]
    )

 

Thank you very much

Need Help
  • Nishantjain's avatar
    Nishantjain
    5 years ago

    Anonymous The picture is not clear so I can't see what value you have in the month filter. 

     

    I think the below code should work but might need some tweak to fit to you model. Give it a go and let me know if this doesn't work

     

        IF (
    	// find if the unrelated calender is filtered or not. 
    	
        ISFILTERED ( CalendarUnrel[Fiscal Month] ),
        
        // if unrelated calender is filtered, calculate the value for a single month
        CALCULATE (
            [Production],
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar]r'[Fiscal MonthNumber] = MAX ( 'CalendarUnrel'[Fiscal MonthNumbe )
            )
        ),
        // if unrelated calender is not filtered, calculate the value for the total column
        CALCULATE (
            [Production],
            FILTER (
                ALL ( 'Calendar' ),                
                // you may have to change this to make sure the "more than" and "less than" works properly
                'Calendar'[Fiscal MonthNumber] >= "001" 
                    && 'Calendar'[Fiscal MonthNumber] <= MAX ( 'CalendarUnrel'[Fiscal MonthNumber] )
            )
        )
    )

    Thanks

    Nishant

3 Replies

  • Nishantjain's avatar
    Nishantjain
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Anonymous The picture is not clear so I can't see what value you have in the month filter. 

     

    I think the below code should work but might need some tweak to fit to you model. Give it a go and let me know if this doesn't work

     

        IF (
    	// find if the unrelated calender is filtered or not. 
    	
        ISFILTERED ( CalendarUnrel[Fiscal Month] ),
        
        // if unrelated calender is filtered, calculate the value for a single month
        CALCULATE (
            [Production],
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar]r'[Fiscal MonthNumber] = MAX ( 'CalendarUnrel'[Fiscal MonthNumbe )
            )
        ),
        // if unrelated calender is not filtered, calculate the value for the total column
        CALCULATE (
            [Production],
            FILTER (
                ALL ( 'Calendar' ),                
                // you may have to change this to make sure the "more than" and "less than" works properly
                'Calendar'[Fiscal MonthNumber] >= "001" 
                    && 'Calendar'[Fiscal MonthNumber] <= MAX ( 'CalendarUnrel'[Fiscal MonthNumber] )
            )
        )
    )

    Thanks

    Nishant

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    This will be a memory intensive calculation.  The easiest solution is to let the user select multiple months in the slicer rather than select just one month.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks Nishantjain for you help.

    I have followed your tips, and with few little changes I came up with the solution. Thanks again for you support!!

    Production =
    VAR maxmonthselected =
    MAX ( 'Calendar'[Fiscal MonthNumber] )
    VAR mindate =
    STARTOFYEAR ( 'CalendarUnrel'[Date], "30/06" )
    VAR maxdate =
    MAX ( 'CalendarUnrel'[Date] )
    RETURN
    IF (
    ISFILTERED ( CalendarUnrel[Fiscal Month] ),
    IF (
    MAX ( CalendarUnrel[Fiscal MonthNumber] ) <= maxmonthselected,
    CALCULATE (
    [Produccion],
    FILTER (
    ALL ( 'Calendar' ),
    'Calendar'[Fiscal MonthNumber] = MAX ( 'CalendarUnrel'[Fiscal MonthNumber] )
    && 'Calendar'[Fiscal Year] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
    )
    ),
    IF ( MAX ( CalendarUnrel[Fiscal MonthNumber] ) > maxmonthselected, BLANK () )
    ),
    CALCULATE (
    CALCULATE ( [Produccion], DATESYTD ( 'Calendar'[Date], "30/06" ) ),
    FILTER ( 'Calendar', 'Calendar'[Date] <= maxdate )
    )
    )