Forum Discussion
Identifying State history Count based on date slicer
HI ManuBK,
I have recreated your requirement in Power BI using sample data and attached a sample PBIX file for your reference. You may also find the following Microsoft documentation helpful, as it aligns with this approach.
Slowly changing dimension type 2 - Microsoft Fabric | Microsoft Learn
Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Thank you.
HI ManuBK,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
Super User
Old State Count =
VAR SlicerMin = MIN('Date'[Date])
VAR SlicerMax = MAX('Date'[Date])
RETURN
COUNTROWS(
FILTER(
ADDCOLUMNS(
VALUES('Table'[ID]),
"@State",
CALCULATE(
MAX('Table'[Old State]),
'Table'[Start date] <= SlicerMax,
'Table'[New State Date] >= SlicerMin
)
),
[@State] = SELECTEDVALUE('State'[State])
)
)
Counts IDs where state record overlaps slicer range
Hi Kedar_Pande Thanks for your reply .Apologise for late response. I have three questions on above measure
VAR SlicerMin = MIN('Date'[Date]) As this needs to be as calendar date right ?[@State] = SELECTEDVALUE('State'[State]) The bold value is the old state or the new state as when i copy the code and placed in PBI it says Cannot find the name StateAnd for Calendar date which column has been used for a relationship - Start date or New Start date ?
v-saisrao-msftCommunity Support
HI ManuBK,
I have recreated your requirement in Power BI using sample data and attached a sample PBIX file for your reference. You may also find the following Microsoft documentation helpful, as it aligns with this approach.
Slowly changing dimension type 2 - Microsoft Fabric | Microsoft Learn
Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Thank you.
