Auto-scale Daily/Monthly axis with field parameter – chart still shows wrong grain

I’m trying to get a line chart axis to automatically show either Date (daily) or MonthName (monthly) based on the selected date range, using a field parameter and an auto-scale measure.

Setup:

Date field parameter “Date Hierarchy” with:
Daily: dim_date[Date]
Monthly: dim_date[MonthName]

Helper measure for the selection:

Date Hierarchy Selection =
SELECTEDVALUE ( 'Date Hierarchy'[Name] ) -- returns "Daily" or "Monthly"

Auto-scale measure:

Auto-Scale Date Filter =
VAR MonthCount =
DATEDIFF (
MIN ( 'dim_date'[Date] ),
MAX ( 'dim_date'[Date] ),
MONTH
)
VAR Selection = [Date Hierarchy Selection]
// Daily = up to 1 month
VAR DailyScale =
Selection = "Daily"
&& MonthCount <= 1
// Monthly = between 2 and 11 months
VAR MonthlyScale =
Selection = "Monthly"
&& MonthCount >= 2
&& MonthCount < 12
RETURN
IF ( DailyScale || MonthlyScale, 1, 0 )

I use Auto-Scale Date Filter as a visual-level filter on the line chart (is 1). My goal:

- If the user picks a short range (e.g. a few days, <= 1 month) → chart uses Date (Daily)

- If the user picks a longer range (2–11 months) → chart uses MonthName (Monthly).

However, when I select about 3 months and have “Daily” chosen, Auto-Scale Date Filter = 0 in a card, but the chart still shows data and doesn’t switch/hide the wrong level as I expect. I just want the X-axis to show the correct grain (Date vs MonthName) for the range.

What am I missing in terms of using this Auto-Scale measure together with the Date field parameter so that the chart’s axis always shows the proper date level? OR it is empty if I am selecting out of range date scope?

Link to my database example:

https://drive.google.com/file/d/1F1e2tClUStXmS8sWxo_tt0UZtlFNfMWS/view?usp=sharing

edit: one more bonus question. I want to show text for user something like "Please choose appropriate date range", how can I add this to specific visual?

Best,
Jacek

Need Help

jaryszek

6 months ago

Ok I solved the issue myself.

Auto-Scale Date Filter = 
VAR MinDate =
    CALCULATE (
        MIN ( 'dim_date'[Date] ),
        ALLSELECTED ( 'dim_date' )
    )

VAR MaxDate =
    CALCULATE (
        MAX ( 'dim_date'[Date] ),
        ALLSELECTED ( 'dim_date' )
    )

VAR DayCount =
    DATEDIFF ( MinDate, MaxDate, DAY ) + 1

VAR MonthCount =
    DATEDIFF ( MinDate, MaxDate, MONTH )

VAR Selection = [Date Hierarchy Selection]

// Daily = up to 31 days
VAR DailyScale =
    Selection = "Daily"
        && DayCount <= 31

// Monthly = between 2 and 11 months
VAR MonthlyScale =
    Selection = "Monthly"
        && MonthCount >= 1
        && MonthCount < 12

RETURN
    IF ( DailyScale || MonthlyScale, 1, 0 )

Dax was not working.

21 Replies

DanieleUgoCopp
Super User
7 months ago
Hello,
I think instead of filtering the visual in general, you need to make something with the axis itself, the usual workaround is to check which date level is currently in scope and only allow it when the selected date range makes sense, if the grain doesn’t match the range, the visual just goes empty,
something like these lines should works:

Show Axis = VAR MonthCount = DATEDIFF ( MIN ( dim_date[Date] ), MAX ( dim_date[Date] ), MONTH ) RETURN SWITCH ( TRUE(), ISINSCOPE ( dim_date[Date] ) && MonthCount <= 1, 1, ISINSCOPE ( dim_date[MonthName] ) && MonthCount >= 2 && MonthCount < 12, 1, 0 )
put the Date Hierarchy field parameter on the X-axis, and use Show Axis = 1 as a visual-level filter
- jaryszek
  Super User
  7 months ago
  Ok thank you,
  
  Why my dax is not working? Why this is different than yours? Why to use IsInScope at all ?
  
  Unfortunately it does not work:
  
  Best Wishes,
  Jacek
kushanNa
Super User
7 months ago
Hi jaryszek

you can follow this tutorial to get a dynamic x axis https://www.youtube.com/watch?v=hilfglpKNRQ

I have attached the sample I have created for your reference

sample1.pbix162 KB
- jaryszek
  Super User
  7 months ago
  Thank you,
  
  this is a different method.
  
  curious:
  Date Filter = VAR SlicerSelection = SELECTEDVALUE('Slicer Selection'[Type]) VAR CurrentType = SELECTEDVALUE('Dynamic Date Scope'[Type]) VAR NumDays = DATEDIFF(MIN(dim_date[Date]),MAX(dim_date[Date]),DAY) RETURN SWITCH( TRUE(), SlicerSelection = "Daily" && CurrentType = "Daily", 1, SlicerSelection = "Monthly" && CurrentType = "Monthly", 1, SlicerSelection = "Yearly" && CurrentType = "Yearly", 1, SlicerSelection = "Dynamic" && NumDays <= 90 && CurrentType = "Daily", 1, SlicerSelection = "Dynamic" && NumDays > 90 && NumDays <= 729 && CurrentType = "Monthly", 1, SlicerSelection = "Dynamic" && NumDays >= 730 && CurrentType = "Yearly", 1, 0 )
  
  How this statement can work?
  SlicerSelection = "Dynamic" && NumDays <= 90 && CurrentType = "Daily", 1,
  
  CurrentType is VAR CurrentType = SELECTEDVALUE('Dynamic Date Scope'[Type])
  so it means that this is "Dynamic" only, not "Daily" in the same time?
  
  Best,
  Jacek
  - kushanNa
    Super User
    7 months ago
    Hi jaryszek
    
    Based on the tutorial I provided, the design seems to work as follows: if your selection is dynamic and the data range is less than 90 (you can change this to match your requirement), it change the current type to Daily by putting 1 in front of it—similar to what happens when you manually select Daily.
    
    You can observe this behavior if you create a table with 'Dynamic Date Scope'[Type] and Datafilter as columns. You’ll see that Daily changes to 1, which is the same result as selecting Daily from the dropdown.
    
    *edited: no need to disable the relationship
v-prasare
Community Support
7 months ago

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
- jaryszek
  Super User
  7 months ago
  thank you,
  
  let's keep this open. I have exact example attached with specific model to be fixed/updated.
  There is no answer for that.
  
  Best,
  Jacek
v-prasare
Community Support
7 months ago
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
jaryszek
Super User
7 months ago
Anyone can fix this sample model?

Thank you!
Jacek
- kushanNa
  Super User
  7 months ago
  Hi jaryszek
  
  This solution should have both options you need manually daily monthly selection and also auto scale with your data , if you can explain the issue or correct output in this sample , maybe we could be able to help you to fix it ?
  
  https://community.fabric.microsoft.com/t5/Desktop/Auto-scale-Daily-Monthly-axis-with-field-parameter-chart-still/m-p/4926330#M1456458
  - jaryszek
    Super User
    7 months ago
    hello,
    
    ok what is not clear in my first post? I explained everything what i have, what measures i have and I attached sample database.
    
    How to make my date selection dynamic on the chart?
    
    Best,
    Jacek
v-prasare
Community Support
6 months ago
Hi jaryszek,

were you able to raise this issue with the Power BI certified solution partners? If you’ve received any updates from them, we’d really appreciate it if you could share the solution here as well. It could be helpful for others in the community facing something similar.

if you still need any help from our side, feel free to post your questions here anytime. We’re always happy to support.

thanks,

Prashanth
- jaryszek
  Super User
  6 months ago
  Sorry, but no. It is a waste of my time. Partners are mostly paid additionally, and their quality of service is very,very poor.

jaryszek

Super User

6 months ago

Ok I solved the issue myself.

Auto-Scale Date Filter = 
VAR MinDate =
    CALCULATE (
        MIN ( 'dim_date'[Date] ),
        ALLSELECTED ( 'dim_date' )
    )

VAR MaxDate =
    CALCULATE (
        MAX ( 'dim_date'[Date] ),
        ALLSELECTED ( 'dim_date' )
    )

VAR DayCount =
    DATEDIFF ( MinDate, MaxDate, DAY ) + 1

VAR MonthCount =
    DATEDIFF ( MinDate, MaxDate, MONTH )

VAR Selection = [Date Hierarchy Selection]

// Daily = up to 31 days
VAR DailyScale =
    Selection = "Daily"
        && DayCount <= 31

// Monthly = between 2 and 11 months
VAR MonthlyScale =
    Selection = "Monthly"
        && MonthCount >= 1
        && MonthCount < 12

RETURN
    IF ( DailyScale || MonthlyScale, 1, 0 )

Dax was not working.

Forum Discussion

Auto-scale Daily/Monthly axis with field parameter – chart still shows wrong grain

21 Replies

Related content

Monthly sales budget data allocation to daily grain

[HELP - URGENT] Auto Scaling Microsoft Fabric Power BI

Monthly Budget to Daily

Monthly into Daily Resolution

Switch between daily and monthly fact tables with slicer