Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Reuben
Helper III
Helper III

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?

Matrixfiltered.png

 

 

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

1 ACCEPTED SOLUTION
Nishantjain
Continued Contributor
Continued Contributor

@Reuben 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

View solution in original post

3 REPLIES 3
Reuben
Helper III
Helper III

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 )
)
)

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nishantjain
Continued Contributor
Continued Contributor

@Reuben 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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.