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,

I went through your example. 

First of all, you are adding columns as dates using ADDColumns Dax into X-axis.
I am using a field parameter there, which is connected to the calculation group.

 



and my measure to filter is like that:



But this is not working. I do not know why...

Best,
Jacek

kushanNa's avatar
kushanNa
Icon for Super User rankSuper User
7 months ago

Okay, could you please let me know what issues or limitations you might face if you use a table column for x axis (as I did) instead of a field parameter connected to calculated group (as you did)?

 

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

    I need to have daily/monthly switchers in tables by design. So business wants in all pages have a way to switch between different kind of granularities within report tables. 

    This is why I am forced to use field parameter. 
    Is it no possible with dynamic selection and field parameter?

    Best,
    Jacek

    • kushanNa's avatar
      kushanNa
      Icon for Super User rankSuper User
      7 months ago

      jaryszek 

       

      To be honest, I’m not entirely sure how to handle date granularity using field parameters, but check whether this workaround works for you?

      I added a new dummy row to your field parameter called Dynamic and created a relationship between my slicer selection and your field parameter (or if you do not like to create new relationship, you can update the Datefilter mesure to use your field parameter as well). This way, when you make a selection in the field parameter, the dynamic date is picked up accordingly and the visual updates as expected.

      This allows you to keep your existing field parameter logic for other requirements, while ensuring the visual also works correctly with it. attached is the samlpe I have created 

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

        Thank you very much, 

        it is very helpful.

        Ok I have some issues:

        1) 


        When you are choosing granularity "Dynamic" value , thus "Dynamic" is not present in calc group, we do not have any corresponding selection out there...
        What to put as calc item to have also Dynamic Selection out there in calc group? 



        Currently, Daily is taking data from Fct_AmortizedCosts Daily and Monthly is taking data from the Monthly Fact table. If we have dynamic it should change sometimes to Daily, sometimes to Monthly. 


        2) Dynamic filter on field parameter works fine.

        Thank you,
        Jacek