Forum Discussion

jaryszek's avatar
jaryszek
Icon for Super User rankSuper User
7 months ago
Solved

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: Da...
Need Help
  • jaryszek's avatar
    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. 


  • jaryszek's avatar
    jaryszek
    Icon for Super User rankSuper 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's avatar
      kushanNa
      Icon for Super User rankSuper 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