Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Month | Store | Product | Value | Daily Value (Value / number of days in the month) |
January 2024 | X | X | 93 | 93/31=3 |
The table has relationship with Calendar table as many-1 (Month column is formatted as the date 01.01.2024)
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]
)
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
25 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
33 | |
27 | |
16 | |
14 | |
13 |