topic Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month in DAX Commands and Tips

Filter monthyear chart x-axis using a measure rather than manually selecting the month

Johnners — Fri, 09 Jun 2023 19:34:56 GMT

Hi.

I have the chart below and I want to filter the x-axis based on the versionMonth slicer selection on the top right. For example, With it set to Dec-22 I want only the columns for Dec-22, Nov-22, Sep-22, June-22, Mar-22 and Dec-21 to appear. I can obviously do it manually using the Filter pane but is there a way to do it using a measure. I have attached my pbix.

What I have:

What I want via a measure but did it manually:

Many Thanks

Link to model

Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month

some_bih — Sat, 10 Jun 2023 07:38:06 GMT

Hi Johnners I understand that your measure is used for both PY and CY for your graph, so one possible solution to have "fix" list of dates is measure below.

I duplicate zour visual and insert data label to check numbers and colored the same blue color for bars as shown on pictute below. I hope this help

Hard code filter test =

--in VAR below choose list of dates to provide future dates

VAR _FilterMonhtYear = TREATAS( {"Mar-22","Jun-22","Sep-22","Nov-22", "Dec-22","Dec-21"},

'Calendar'[MonthYear]

)

VAR SelectedCountryL1 = SELECTEDVALUE(Countries[L1],"")

VAR SelectedCountryL2 = SELECTEDVALUE(Countries[L2],"")

VAR SelectedCountryL3 = SELECTEDVALUE(Countries[L3],"")

VAR SelectedCountryL4 = SELECTEDVALUE(Countries[L4],"")

VAR SelectedCountryL5 = SELECTEDVALUE(Countries[L5],"")

VAR SelectedCountryL6 = SELECTEDVALUE(Countries[L6],"")

RETURN

CALCULATE([Len_Amt],

_FilterMonhtYear,--from above list of dates

Countries[L1] = SelectedCountryL1,

Countries[L2] = SelectedCountryL2,

Countries[L3] = SelectedCountryL3,

Countries[L4] = SelectedCountryL4,

Countries[L5] = SelectedCountryL5,

Countries[L5] = SelectedCountryL6

)

Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month

Johnners — Mon, 12 Jun 2023 11:20:47 GMT

Hi Some_bih,

Great thanks. I am trying to expand this to use a Switch depending on the reportdate as follows. However I get an error message:

Any idea how to get around this?

Thanks

Tim

LenCY =

VAR SelectedCountryL1 = SELECTEDVALUE(Countries[L1],"")

VAR SelectedCountryL2 = SELECTEDVALUE(Countries[L2],"")

VAR SelectedCountryL3 = SELECTEDVALUE(Countries[L3],"")

VAR SelectedCountryL4 = SELECTEDVALUE(Countries[L4],"")

VAR SelectedCountryL5 = SELECTEDVALUE(Countries[L5],"")

VAR SelectedCountryL6 = SELECTEDVALUE(Countries[L6],"")

VAR monthrep = [Version]

//VAR _FilterMonhtYear = TREATAS( {"Mar-22","Jun-22","Sep-22","Nov-22", "Dec-22","Dec-21"},'Calendar'[MonthYear])

VAR _FilterMonhtYear =

Switch(monthrep,

"Nov-22",TREATAS( {"Mar-22","Jun-22","Sep-22","Nov-22", "Dec-22","Dec-21"},'Calendar'[MonthYear]),

"Dec-22",TREATAS( {"Mar-22","Jun-22","Sep-22","Nov-22", "Dec-22","Dec-21"},'Calendar'[MonthYear])

)

VAR SelectedYear = "2022"

VAR Result = CALCULATE([Len_Amt],_FilterMonhtYear,Countries[L1] = SelectedCountryL1,Countries[L2] = SelectedCountryL2,Countries[L3] = SelectedCountryL3,Countries[L4] = SelectedCountryL4,Countries[L5] = SelectedCountryL5,Countries[L5] = SelectedCountryL6)

RETURN

Result

Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month

some_bih — Mon, 12 Jun 2023 11:58:23 GMT

Hi Johnners we have progress 🙂 Explain what you wanto to show / calculate and share model as I only see error not your code

Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month

Johnners — Mon, 12 Jun 2023 12:31:17 GMT

Hi some_bih,

Sorry I now have uploaded the model:

Link to Model

I want the months passed to the graph dependent on the month chosen in the slicer. The code giving the error is as follows:

LenCY =

VAR SelectedCountryL1 = SELECTEDVALUE(Countries[L1],"")

VAR SelectedCountryL2 = SELECTEDVALUE(Countries[L2],"")

VAR SelectedCountryL3 = SELECTEDVALUE(Countries[L3],"")

VAR SelectedCountryL4 = SELECTEDVALUE(Countries[L4],"")

VAR SelectedCountryL5 = SELECTEDVALUE(Countries[L5],"")

VAR SelectedCountryL6 = SELECTEDVALUE(Countries[L6],"")

VAR monthrep = [Version]

//VAR _FilterMonhtYear = TREATAS( {"Mar-22","Jun-22","Sep-22","Nov-22", "Dec-22","Dec-21"},'Calendar'[MonthYear])

VAR _FilterMonhtYear =

Switch(monthrep,

"Nov-22",TREATAS( {"Mar-22","Jun-22","Sep-22","Nov-22", "Dec-22","Dec-21"},'Calendar'[MonthYear]),

"Dec-22",TREATAS( {"Mar-22","Jun-22","Sep-22","Nov-22", "Dec-22","Dec-21"},'Calendar'[MonthYear])

)

VAR SelectedYear = "2022"

RETURN

Result

Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month

some_bih — Mon, 12 Jun 2023 12:38:21 GMT

Hi Johnners in measure, part TREATAS there are already months "selected" / Filtered so it will be hardly to add slicered months to already "slicered" months. I did not have chance to check your uploaded model / file but without watching, what expected output you want to see? Already filtered months AND add user interaction to choose additionaly for some months (maybe this could work) but results could be ambigious).

Alternative is to have 2 dashboards, literally one with already filtered moths (like we already did) and second with simple measure so user choose dates periods.

What do you think?

Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month

Johnners — Mon, 12 Jun 2023 12:54:55 GMT

Hi,

The reportversion does not filter and it is not linked to the Calender. I thought the issue may be syntax as without the switch it works. I need a different set of months to show in the graph depending on the reportversion chosen in the slicer.

Thanks

Tim

Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month

some_bih — Mon, 12 Jun 2023 13:10:20 GMT

Hi Johnners I will check new model and let you know.

Re: Filter monthyear chart x-axis using a measure rather than manually selecting the month

some_bih — Mon, 12 Jun 2023 20:38:25 GMT

Hi Johnners on picture below there is comparison of two measure used in graph, LenPY and LenCY. Comparing these 2 in LenPY, part

'Calendar'[Year]=SelectedYear,

is extra part as it calculates year 2021.
Remove it and insert date slicer as you wish and check your results