Filter-agnostic variable

[RMDNA]

I have a variable, Win Revenue, that is affected by Fiscal Year, due to table schema. I need Win Revenue to just ignore when Fiscal Year changes. Is there any way to do that with custom columns/etc.?

If so, I now need a different variable to change based on the value of fiscal year, and have that apply as a filter to Win Revenue.

e.g. if I change Fiscal Year to FY17, I don't want that to directly affect Win Revenue (although it normally would), but I do need to filter Win Revenue by different critera now that FY has changed.

I'll help with clarification where I can. I'm having a lot of issues trying to explain this.

[MFelix]

Hi @RMDNA,

From what I can uderstand from your text you need a variable that overlooks the Fiscal Year to do this in your measure you need to use the ALL function that allows you to "overlook" the filter that you may have in your table /visual and consider the full table/column to make the calculation.

To have a different variable affecting your measure you need to apply a FILTER on your measure, both of this need to be applied by CALCULATED  formula, but without further information it's hard to give you a more complete answer.

Please check this link and also this where you have several examples of the usage of filtering in the measure.

This sites are from @marcorusso and @AlbertoFerrari that make very compreensive articles on DAX.

As I said if you provide any addtional information or data samples I can help you better.

Regards,

MFelix

[RMDNA]

Hi @MFelix,

I'm familiar with FILTER and CALCULATE, but not ALL/ALLEXCEPT.

So Revenue Won is:

Total Revenue Won = 
CALCULATE(
	SUM('Table 1'[Total Revenue]),
	       TREATAS({("Won")}, 'Table 1'[Status]
                )
         )

Fiscal Year is a field in a different table. From looking at the examples, I'm having trouble figuring out where to insert our ALL/ALLEXCEPT. Any help?

Once this works, I'll probably be able to get the second part on my own.

[v-yuezhe-msft]

@RMDNA,

You can create the measure using FILTER function or INSPECT function instead, then add the ALL/ALLEXCEPT function into your DAX formula, for more details, please review this blog: http://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/ .

If you have any questions about the DAX, please share sample data of your tables and post expected result here.

Regards,
Lydia