Forum Discussion
Switch result not as expected
Hello,
I am trying to create a new table based on the formula below. Ultimately, this table is a bridge within my data model in order for me to be able to create a "smart axis" for a chart - all credit for this goes to: How to Power BI on Youtube.
The problem I am running into is that my Variable OR Switch function (not sure which) is not returning the intended result. I created a standalone measure to validate the Variable, and it is returning the correct intended result. Regardless of how I filter the dates, my period column will always return "4". I have tried to troubleshoot and am not able to come to solution.
DateToPeriodBridge =
ADDCOLUMNS(
CALENDAR(MIN(Table[OrderDate]),MAX(Table[OrderDate])),
"Period",
VAR MonthCount = DATEDIFF(MIN(DateDim[FullDate]),[Date],MONTH)
RETURN
SWITCH(
TRUE(),
MonthCount = 0, 0, -- Date break
MonthCount <= 2, 1, -- Week
MonthCount <= 3, 2, -- Month
MonthCount <= 12, 3, -- Quarter
4 --Year
)
)
2 Replies
ExcelMonke , As this a table it will take min and max order_date value without using any filter and slicer, so you will always get static number
Convert to Measure
example
DateToPeriodBridge =
Maxx( ADDCOLUMNS(
CALENDAR(MIN(Table[OrderDate]),MAX(Table[OrderDate])),
"Period",
VAR MonthCount = DATEDIFF(MIN(DateDim[FullDate]),[Date],MONTH)
RETURN
SWITCH(
TRUE(),
MonthCount = 0, 0, -- Date break
MonthCount <= 2, 1, -- Week
MonthCount <= 3, 2, -- Month
MonthCount <= 12, 3, -- Quarter
4 --Year
)
), [Period])You can dynamic Segmentation to use measure on axis
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kDynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ
Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q
Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4
Thank you, this was helpful to better understand what may be going on with my table. Although, the links you posted do not quite get me to what I am hoping to achieve.
My goal is to replicate the following:
https://www.youtube.com/watch?v=KReYWx5NXYg
My problem is that, when using "CalendarAuto" i continue getting circular dependency issues
