Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I have the below schema
The DatePeriod can be Yesterday, This Week, This Month, etc. So a Date can be in multiple Date Periods.
Date Period has Many to One relationship with DimDate.
If I use the DimDate date column as slicer and use ALLSELECTED in a measure to select Max Sales for the period, it works well.
But if I remove the DimDate slicer and use the Date Period as a slicer and select This Week, which in turn should filter DimDate which in turn should filter FactInternetSales.
But in this scenario, the Max Sales is displayed for all the dates in DimDate.
Why is this?
I can include a IF [_Sales Amount] > 0 check and restrict it to show only the required dates, but I ma trying to understad the ALLSELCTED behaviour in snowflake scema scenario.
Here is the measure I am using:
_Max Sales =
var _SalesSummary =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(FactInternetSales, DimDate[FullDateAlternateKey])
,"@Amt" ,[_Sales Amount]
)
,ALLSELECTED()
)
var _MaxSales =
MAXX(
_SalesSummary
,[@Amt]
)
RETURN
_MaxSales
I can't SUMMARIZE FactInterSales by DatePeriod table. I am assuming the relationship cannot traverse many to one, eventhough it is set to filter DimDate table. Is this the cause?
How can I solve this scenario in data modelling?
Date Period has Many to One relationship with DimDate.
yes, but the search filter goes both ways, making this rather ineffective. Cut the link and use measures instead.
Thanks @lbendlin .
The users need to be able to filter by Date Period. e.g. This week, this month, etc. So they don't need to enter dates - that is the requirement.
I cannot use a measure as a Slicer.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
59 |
User | Count |
---|---|
197 | |
116 | |
107 | |
77 | |
70 |