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

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 

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    7 months ago

    we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs. You can explore the list of trusted partners here:

    Power BI Partners | Microsoft Power Platform

    You’re always welcome to continue engaging with the community as well,
    Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.
    we truly appreciate your active participation in the Microsoft Fabric Community

  • 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