topic Re: Removefilters?? All??? AllSelected??? which to use? in DAX Commands and Tips

Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 14:13:51 GMT

Question: How do I modify my measure to ignore the date slicer?

Details:

I have a cluster chart that captures the average number of tickets per week of the month for this month and last month. I am also trying to add a line that captures the average number of tickets per week of the month for ALL months, but I can't seem to make it work. I'm not sure which command function to use or where to apply it within the formula.

Here is the data that drives the bars above in table format since I can't upload anything to the cloud.

As you can see from the chart above, because April is selected in the Date slicer, I'm getting the correct bar values but the line chart is simply using the selected month. What I need is for the line chart to pick up these values (which are the weekly average counts for all months):

The measure to modify:

This is the formula that drives the above Weekly Avg I want:

_Inc SC Avg Count by W# = AVERAGEX ( VALUES( 'DATE Table'[_Date SC Week of Month (# only)] ), 'Incident Measures'[_Inc Count] ) / [_Count of Months]

The biggest hurdles I'm facing right now is that (1) all the parts of my above measure are also measures (2) that if I remove any of the date filters, the other parts of the formula don't work and (3) what I want to ignore is a slicer (I think, for example, that REMOVEFILTERS only works on actual filters, but I'm not sure).

Re: Removefilters?? All??? AllSelected??? which to use?

daXtreme — Tue, 17 May 2022 14:36:40 GMT

This won't work?

[Meas] = CALCULATE( [_Inc SC Avg Count by W#], // just remove the filter // that comes from the slicer... ALL( Dates[Month] ) )

You just have to remove the filter from the value Apr-2022. Then, the measure above should see all the months and only the week that's on the x-axis.

Re: Removefilters?? All??? AllSelected??? which to use?

tamerj1 — Tue, 17 May 2022 14:38:52 GMT

Hi Anonymous
Please provide some sample data

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 14:44:20 GMT

That's what I thought too but the following doesn't work (where Year Month Name is the column in my date table that is the slicer):

_Inc SC Avg Count by W# 2 = CALCULATE('Incident Measures'[_Inc SC Avg Count by W#],ALL('DATE Table'[Year Month Name]))

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 14:58:24 GMT

The column headers run together but they are

Year Month Name

_Date SC Week of Month (W#)

_Inc Count

Year Month Name	_Date SC Week of Month (W#)	_Inc Count
Dec-21	W1	2,841
Dec-21	W2	7,503
Dec-21	W3	7,417
Dec-21	W4	5,226
Jan-22	W1	6,482
Jan-22	W2	7,896
Jan-22	W3	8,501
Jan-22	W4	10,396
Feb-22	W1	8,439
Feb-22	W2	8,636
Feb-22	W3	8,614
Feb-22	W4	8,199
Mar-22	W1	8,232
Mar-22	W2	8,166
Mar-22	W3	8,434
Mar-22	W4	13,457
Apr-22	W1	8,733
Apr-22	W2	8,028
Apr-22	W3	7,328
Apr-22	W4	9,620
May-22	W1	7,571
May-22	W2	6,758

The date table contains the following entry that drives the date slicer:

DATE Table =

var _fromYear=2020

var _toYear=YEAR(TODAY())

var _today=TODAY()

return

ADDCOLUMNS(

CALENDAR(

DATE(_fromYear,5,1),

DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))),

"Year Month Name",FORMAT([Date],"MMM-YYYY"),

)

Re: Removefilters?? All??? AllSelected??? which to use?

tamerj1 — Tue, 17 May 2022 15:02:27 GMT

Hi Anonymous
Please try

_Inc SC Avg Count by W# = AVERAGEX ( VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ), CALCULATE ( [_Inc Count], ALL ( 'DATE Table'[Year Month Name] ) ) ) / [_Count of Months]

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 15:18:03 GMT

tamerj1

😕 It still returns the values from the month selected in the date slicer.

Please please please keep trying.

Re: Removefilters?? All??? AllSelected??? which to use?

tamerj1 — Tue, 17 May 2022 15:30:19 GMT

Anonymous
I think you have more columns in the date table than mentioned in the code. Howeve, Please try

_Inc SC Avg Count by W# = AVERAGEX ( VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ), CALCULATE ( [_Inc Count], ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] ) ) ) / [_Count of Months]

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 15:45:50 GMT

That returned this:

Yes, my date table has many more columns and quite a few measures:

This is my master table with other relevant extra columns added at the bottom (I made it smaller to take up less space):

DATE Table =

var _fromYear=2020

var _toYear=YEAR(TODAY())

var _today=TODAY()

return

ADDCOLUMNS(

CALENDAR(

DATE(_fromYear,5,1),

DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))),

"Year",YEAR([Date]),

"Start of Year",DATE( YEAR([Date]),1,1),

"End of Year",DATE( YEAR([Date]),12,31),

"Month",MONTH([Date]),

"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),

"End of Month",EOMONTH([Date],0),

"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,

"Year Month Number",INT(FORMAT([Date],"YYYYMM")),

"Year Month Name",FORMAT([Date],"MMM-YYYY"),

"Date (dd-mmm-yy)",[Date],

"Day",DAY([Date]),

"Day Name Long",FORMAT([Date],"DDDD"),

"Day Name Short",FORMAT([Date],"DDD"),

"Day of Week",WEEKDAY([Date]),

"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,

"Month Name Long",FORMAT([Date],"MMMM"),

"Month Name Short",FORMAT([Date],"MMM"),

"Quarter",QUARTER([Date]),

"Quarter Name","Q"&FORMAT([Date],"Q-YYYY"),

"Year Quarter Number",INT(FORMAT([Date],"YYYYQ")),

"Year Quarter Name",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),

"Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),

"End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),

"Week of Year",WEEKNUM([Date]),

"Start of Week", [Date]-WEEKDAY([Date],3),

"End of Week",[Date]-WEEKDAY([Date],3)+6,

"End of Week (dd-mmm-yy)",[Date]-WEEKDAY([Date],3)+6,

"Day Offset",DATEDIFF(_today,[Date],DAY),

"Month Offset",DATEDIFF(_today,[Date],MONTH),

"Quarter Offset",DATEDIFF(_today,[Date],QUARTER),

"Year Offset",DATEDIFF(_today,[Date],YEAR)

)

Other key date columns and measures:

_Date SC Week of Month (W#) = IF(DIVIDE(CEILING(DAY('DATE Table'[Date]),7),7) >= 4, "W4", "W" & DIVIDE(CEILING(DAY('DATE Table'[Date]),7),7))

_Date SC Week of Month (# only) = IF(DIVIDE(CEILING(DAY('DATE Table'[Date]),7),7) >= 4, 4, DIVIDE(CEILING(DAY('DATE Table'[Date]),7),7))

_Count of Months = COUNTROWS(VALUES('DATE Table'[Year Month Name]))

_Inc Count = IF (ISBLANK (DISTINCTCOUNT ('INCIDENTS'[INC_NUMBER])), BLANK(), DISTINCTCOUNT ('INCIDENTS'[INC_NUMBER]))

The column called "Week" is the _Date SC Week of Month (W#) column.

Count of Months and Inc Count are both measures.

Re: Removefilters?? All??? AllSelected??? which to use?

daXtreme — Tue, 17 May 2022 15:52:47 GMT

You may have a problem like the one above if you have columns in your date table which are sorted by other columns. This is a known problem and you can find a solution to this on YT in one of Alberto Ferrari's videos. But long story short, you have to not only remove the filter from the column you use in the slicer but also remove it from the field by which you sort the column. Not saying this IS the problem here but only that it might be.

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 15:53:27 GMT

tamerj1

For what it's worth, when I trigger the slashed circle on the table (with the date slicer selected), the results are what we want. Unfortunately, we need that functionality to work on the measure itself.

Re: Removefilters?? All??? AllSelected??? which to use?

tamerj1 — Tue, 17 May 2022 16:10:10 GMT

Anonymous

There should be a solution. Please try

_Inc SC Avg Count by W# = CALCULATE ( AVERAGEX ( VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ), [_Inc Count] ), ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] ) ) / [_Count of Months]

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 16:15:49 GMT

There has to be a solution but so far, nada. This produced the overall average for each week from the previous measure:

Re: Removefilters?? All??? AllSelected??? which to use?

tamerj1 — Tue, 17 May 2022 16:32:40 GMT

Anonymous

We will keep trying

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 16:46:39 GMT

tamerj1

THANK YOU FOR STAYING WITH THIS!!!! 😟 This time we got the daily average, I think:

Re: Removefilters?? All??? AllSelected??? which to use?

tamerj1 — Tue, 17 May 2022 16:48:54 GMT

Anonymous

Can you provide the expected results?

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 16:58:44 GMT

tamerj1

It's the average of all W1s, all W2s, etc. respectively.

When I use my original formula (below), I get these values if I (1) clear the filters from the Date slicer or (2) if I circle slash the table with the Date slicer selected.

(1)

or this

(2)

_Inc SC Avg Count by W# = AVERAGEX ( VALUES( 'DATE Table'[_Date SC Week of Month (# only)] ), 'ABV Incident Measures'[_Inc Count] ) / [_Count of Months]

Re: Removefilters?? All??? AllSelected??? which to use?

Anonymous — Tue, 17 May 2022 17:00:38 GMT

daXtreme

I couldn't find the video you were referring to. I watched a couple of others of his (and Marco's) but they weren't what you were talking about. Can you find a link to it?

Re: Removefilters?? All??? AllSelected??? which to use?

daXtreme — Tue, 17 May 2022 17:21:40 GMT

Side effects of the Sort By Column setting in DAX - YouTube

Re: Removefilters?? All??? AllSelected??? which to use?

tamerj1 — Tue, 17 May 2022 18:17:21 GMT

Anonymous

What is the code of [_Count of months]?