12 Total :=
SWITCH (
TRUE (),
HASONEVALUE ([level04] ), CALCULATE (
[Local],
DATESINPERIOD (
Calendar[Date],
MAX ( Calendar[Date] ),
-12,
MONTH
)
),
HASONEVALUE ([level03]),If([level 03]= "calculation:" && [level03]= "Cash:" && [level03]= "Costs:"=>0, Blank(), CALCULATE (
[Local],
DATESINPERIOD (
Calendar[Date],
MAX ( Calendar[Date] ),
-12,
MONTH
)
)
),
HASONEVALUE ( [level02] ), BLANK ()
)
I would like to add condition that if lever 03 = cash, calculation, and cost then return blank to remove sub total rows. I tried something like that and its not working properly it gives me error "value for column level03 cannot be determined in the current context". how can i add that condition in level03 column?
Just a side note, "its not working properly" is tremendously unhelpful in determining the source of problems. Specifically in a Power Pivot/Tabular model, this link provides a list of ways to help answerers help you.
Syntactically, there are some errors. Let's address those and see if the behavior is appropriate before diving into alternate measure definitions.
This section is the problem:
...
,HASONEVALUE([level03])
// Note, you are inconsistent in referring to [level03] and
// [level 03]
,If( // Below you are testing column references against scalar
// literals. DAX doesn't understand how to do this, so you
// have to wrap each reference in a VALUES() function, which
// can be implicitly converted to a scalar value
[level 03] = 'calculation:' // DAX needs double quotes: ""
&& [level03]= 'Cash:' // You have multiple predicates
&& [level03]=' Costs:'=>0 // for a single field combined
// with a logical and - these
// can't all simultaneously be true
,Blank()
,CALCULATE(
[Local]
,DATESINPERIOD(
Calendar[Date],
MAX ( Calendar[Date] ),
-12,
MONTH
)
)
)
,....
I am just going to change the combination of predicates to a logical or in my rewrite, so that it's possible for this IF() to return a BLANK. The way it's written above, even fixing the syntax errors will cause it to always evaluate the else condition, because [level 03] cannot simultaneously have the value of "calculation:", "Cash:", and " Costs:=>0".
...
,HASONEVALUE('<table>'[level03])
// As a best practice, you should always use fully qualified
// column references - 'table'[field]
// Also, I've referred only to [level03] below
,IF( // Note we've wrapped [level 03] in VALUES() below - this will
// allow our comparisons to scalar literals
VALUES('<table>'[level03]) = "calculation:" // double quotes
|| VALUES('<table>'[level03]) = "Cash:"
// Note: we've changed the logical and, &&, to a logical
// or, || - meaning if any 1 of these predicates is true,
// we'll return BLANK
|| VALUES('<table>'[level03]) = " Costs:=>0"
,BLANK()
,CALCULATE(
[Local]
,DATESINPERIOD(
Calendar[Date]
,MAX( Calendar[Date] )
,-12
,MONTH
)
)
)
,....
What this will do is check for any cell on the pivot table where [level03] has exactly one distinct value - when this is true, it will evaluate the IF() function.
The IF() function will check the value of [level03]. If that value is any one of the three following values, "calculation:", "Cash:", or " Costs:=>0", it will return BLANK. If the value of [level03] is not any of those three, it will evaluate the CALCULATE(), which returns the measure [Local] for a rolling 12 month period.