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 cre...
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