Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ExcelMonke
Super User
Super User

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
    )
)

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





2 REPLIES 2
amitchandak
Super User
Super User

@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-k

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

 

Dynamic 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

 

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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