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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.