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
MFelix's avatar
MFelix
Super User
8 years ago

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'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