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

[Anonymous]

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

[Anonymous]

Ah.  But it doesn't much matter.  I still need to find a way to get to the list of totals that starts with 7,050.  😕

[tamerj1]

Did you see my respose to you other post. Please put both replies to gether in order to get the full picture. 

[tamerj1]

@Anonymous 

Can you provide the expected results?

[Anonymous]

@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]

[tamerj1]

@Anonymous 

What is the code of [_Count of months]?

[Anonymous]

It was one of the Date Table columns I listed above:

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

[daXtreme]

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.

[Anonymous]

@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?

[daXtreme]

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

[Anonymous]

@daXtreme 

Well, if nothing else, that helped me understand a little of what Dax Studio does.  Alas, however, it did not apply in this case.

@tamerj1 

One thing I did notice trying to look at it in Studio is that the first one we tried (with the All filter) completely ignored the All filter, as though it wasn't even there.  Also, when I use the Edit Interactions circle slash, the code doesn't list the date slicer filter as a variable.

If I could figure out how to turn this code into a valid measure, we'd be golden but it's yelling at me around the "IsGrandTotalRowTotal" and Order By parts.

_Inc SC Avg Count by W# 2 =

VAR __DS0FilterTable =

FILTER(

KEEPFILTERS(VALUES('Min CI Service Count'[Min CI Service Count])),

'Min CI Service Count'[Min CI Service Count] = 10

)

VAR __DS0FilterTable2 =

FILTER(

KEEPFILTERS(VALUES('ABV INCIDENTS'[INC_STATE])),

NOT('ABV INCIDENTS'[INC_STATE] IN {"Canceled"})

)

VAR __DS0FilterTable3 =

FILTER(

KEEPFILTERS(VALUES('DATE Table'[Date])),

'DATE Table'[Date] >= DATE(2021, 12, 1)

)

VAR __DS0Core =

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL('DATE Table'[_Date SC Week of Month (W#)], "IsGrandTotalRowTotal"),

__DS0FilterTable,

__DS0FilterTable2,

__DS0FilterTable3,

"v_Inc_SC_Avg_Count_by_W_", 'ABV Incident Measures'[_Inc SC Avg Count by W#]

)

VAR __DS0PrimaryWindowed =

TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'DATE Table'[_Date SC Week of Month (W#)], 1)

RETURN

__DS0PrimaryWindowed

ORDER BY

[IsGrandTotalRowTotal] DESC, 'DATE Table'[_Date SC Week of Month (W#)]