topic Custom Filtered Individual Columns in DAX Commands and Tips

Custom Filtered Individual Columns

JMAlloway — Thu, 16 Jan 2020 15:01:52 GMT

Hello All -

I am looking to create a clustered column chart that shows data from Jan 2017- Dec 2019. I would like to show each year individually by quarter, however, if it is the current quarter, drilldown to months. I already have a date table, but I'm having difficulty figuring out how to write out a DAX argument that groups dates by quarter, except for the current quarter-- show months. Any ideas? Referring to the image below, 'Qtr 4' will need to be broken down into 'October', 'November', and 'December'.

Re: Custom Filtered Individual Columns

Anonymous — Sat, 18 Jan 2020 10:34:22 GMT

Hi JMAlloway ,

Try the following: I added two (DAX) columns to my Date table using the following DAX:

DateAxisValue = 
VAR CurrentQuarter = 4
// QUARTER ( TODAY ())
Return
IF (
QUARTER('Date'[Date]) = CurrentQuarter,'Date'[Month],"Q" & QUARTER('Date'[Date])
)

DateAxisValueSort = 
VAR CurrentQuarter = 4
// QUARTER ( TODAY ())
Return
IF (
QUARTER('Date'[Date]) = CurrentQuarter, "Q" & QUARTER('Date'[Date]) & 'Date'[Month Number],"Q" & QUARTER('Date'[Date])
)

Make sure you change the Sort Column for the DateAxisValue Column to DateAxisValueSort

Then you should be able to produce something like this:

The CurrentQuarter is hardcoded to 4 in the sample, but can also be based current date. Be aware that when the column is calculated there is no filter context active

Jan
if this is a solution for you, don't forget to mark it as such. thanks

Re: Custom Filtered Individual Columns

JMAlloway — Wed, 22 Jan 2020 15:56:51 GMT

Hi Jan -

Thanks for the reply. This looks great! My one issue, is that when enter "QUARTER" on line 6, it's telling me that "The funding used in the expression is not a valid function nor the name of a measure". I currently have a Quarter column in my date table. Do you know what could be wrong?

Thanks!

JMA

Re: Custom Filtered Individual Columns

Anonymous — Wed, 22 Jan 2020 18:29:55 GMT

Hi JMAlloway ,

I think it is a pretty new function (in DAX). I haven't found it yet in the MSDN, but it is in this guide https://dax.guide/quarter/

Otherwise there are various ways to calculate the quarter:

VAR quarterNumber = ISO.CEILING(MONTH([Date])/3,1) or VAR quarterNumber = ROUNDUP(MONTH([Date])/3,0)

These are still usefull since the QUARTER does not take financial years into account.

Jan

Re: Custom Filtered Individual Columns

JMAlloway — Wed, 22 Jan 2020 18:36:15 GMT

Great - everything works out!