Forum Discussion

rawiswarden's avatar
rawiswarden
Helper I
6 years ago
Solved

Cannot Figure Out All and AllExcept

Hello,

 

I have a calculation where I want to ignore every possible filter except one.  I have tried ALL() and ALLEXCEPT() and cannot get it to work.  When I use ALL() for all the dimension tables, the metric is no longer filtered.  When I try to remove the dimension table with the single metric I care about, it does not filter based on that metric.  If I use ALLEXCEPT() with just that single metric, I still have the results filtered by other dimensions in other tables.

 

Here is an example.  I want to calculate this and only allow it to be filtered by Dimension_LossCurves.  Am I doing something wrong or do table relationships impact these functions in a way I do not understand?

 

Cumulative Loss 2 =
VAR __PoolBal = calculate(sum(Dimension_LossCurves[Net Loan]), filter(Dimension_LossCurves, Dimension_LossCurves[Loan Age (Months)] >= max(Facts_LossCurves[Months])), allexcept(Dimension_LossCurves, Dimension_LossCurves[Year Vintage]))

RETURN

VAR __PoolPct = __PoolBal / sum(Dimension_LossCurves[Net Loan])
VAR __CumLoss = calculate(-sum(Dimension_LossCurves[Net CO]), filter(Dimension_LossCurves, Dimension_LossCurves[Loan Age (Months)] >= max(Facts_LossCurves[Months])), filter(Dimension_LossCurves, Dimension_LossCurves[Duration (Months)] <= max(Facts_LossCurves[Months])),allexcept(Dimension_LossCurves, Dimension_LossCurves[Year Vintage]))

RETURN

if(__PoolPct > .75, __CumLoss / __PoolBal, blank())
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi rawiswarden ,

     

    ALL() function will ignore your filters.

    You could share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to OneDrive and share the link here.

     

2 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi rawiswarden ,

     

    ALL() function will ignore your filters.

    You could share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to OneDrive and share the link here.

     

    • rawiswarden's avatar
      rawiswarden
      Helper I
      6 years ago

      Unfortuantely all the data is confidential and the data model is fairly complex, so it'd be hard to randomize.  I'll just calculate this outside of Power BI for now, since I can't seem to get the behavior I'm aiming for.