Forum Discussion

RMDNA's avatar
RMDNA
Solution Sage
8 years ago

Filter-agnostic variable

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.?...
Need Help
RMDNA's avatar
RMDNA
Solution Sage
8 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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