Forum Discussion

PBIry's avatar
PBIry
Regular Visitor
3 years ago

DAX Filter Based on Dynamic Axis Selection

I have a field parameter that will toggle X Axis to show by either Quarter or Month. In my data set, I have attributes "Year", "Quarter" and "Month" (month is represented as #)

 

I am trying to create a dynamic DAX measure that will apply a TOP N filter only if Month is selected as the Date Granularity. So for example, if I select Year = 2022 and Date Granularity = Quarter, I want to see Total Sales by Quarter for 2022. If I select Year = 2022 and Date Granularity = Month, I want to only see the last 6 months. 

 

The reason for this is I do not want to show all 12 bars in my chart due to real estate. I need the functionality to limit to only show the MAX 6 months based on Year selected but only when Month Date Grain is selected, otherwise show the full year broken by quarter. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi PBIry ,

    Could you please provide some sample data? Is it possible to display different data depending on the selected date (year, quarter, month)?  How many tables are there in your file? Is there a one-to-one relationship?

     

    How to Get Your Question Answered Quickly 

     

    Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • PBIry's avatar
      PBIry
      Regular Visitor
      3 years ago

      More details on my use case below:

      • I have 1 summarized table grouping data at the Year-Quarter-Month grain. There is no "date" column with a date data type. Columns below:

         

         

      • I have a simple bar chart showing counts by either Quarter or Month, based on a field parameter called "Date Granularity" by selected year

       

      • My ultimate use case is to have the filter capability where if Date Granularity = Month, i only want to see the latest 6 months for that selected year. For example, assume today is 1/15/2023:
        • If Year = 2021, I should see Jul, Aug, Sep, Oct, Nov, Dec for 2021
        • If Year = 2022, I should see Jul, Aug, Sep, Oct, Nov, Dec for 2022
        • If Year = 2023, I should only see Jan 2023 as we do not have 6 months of data yet. Each month a new bar should appear until Jul 2023 where only the TOP 6 Month Numbers are displayed per year. 

      I am struggling to get this working how I want. I am able to add a TOP N visual level filter using the MAX (or AVG) of Month Number which gives me exactly what I want viewing by Month, plus will work once it becomes 2023. The issue is when viewing by quarter I am only seeing 2 quarters for those 6 months. I need this to essentially ignore the visual filter when quarter is selected.

       

       

       

      I have tried moving the TOP N visual filter to a DAX measure thinking I can wrap this in a switch or if statement to reference the Date Grain selected but I keep getting an error that there are too many scalar values. Another way I was thinking I could do this would be to add some sort of filter that determines how many bars are being displayed and to have a cut off at 6. I could not get that working either unfortuately Any help is appriciated 

       

      Thanks