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
slava_aptown
Frequent Visitor

Set right filter context inside the SUMMARIZE expression

Hi!

I am struggling with keeping and removing certain filter contexts inside a DAX expression.

 

I have a table on monthly granularity. Daily value column is calculated as Monthly value divided by the number of days in a month.

Despite other data is on daily granularity, I do not want to move it to daily as it will be 200 mln rows.

MonthStoreProductValueDaily Value 
(Value / number of days in the month)
January 2024XX9393/31=3


The table has relationship with Calendar table as many-1 (Month column is formatted as the date 01.01.2024)

slava_aptown_0-1707756418333.png

 

I write a measure that calculates Value on daily granularity as 

 

 

Daily Value * number of days chosen in the month

 

 


This is the measure. I struggle at days_chosen. In this column I want to get virtual number of days chosen in the month.
If I do not have any outside filters it should be 31 for January. If I choose only January, 1-10 it should be 10.
I have tried many combinations of ALL, ALLSELECTED, KEEPFILTER etc, but I get either virtual 1 or 31 and it does not respond to my choice in slicer. 

 

 

VAR _tbl1 =
SUMMARIZE(
    table,
    'Calendar'[Month],
    table[store],
    table[product],
    "max_value", MAX(table[value]),
    "days_chosen", 
        COUNTROWS(
            CALCULATETABLE(
                'Calendar',
                'Calendar'[Month] = SELECTEDVALUE('Calendar'[Month]),
                KEEPFILTERS('Calendar'[Day])
            )
        )
)
RETURN
SUMX(
    _tbl1,
    [days_chosen] * [max_value]
)

 

 

 

1 REPLY 1
lbendlin
Super User
Super User

You may want to read this article

 

All the secrets of SUMMARIZE - SQLBI

 

Side question :  What is the number of days in "February"  ?  Which year?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

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!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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