Workaround for TRUE/FALSE Expression error

[KnifeandFork]

Hi all!

I'm desperately trying to get PowerPivot to count the number of 'L's in a column. L itself is a measure, which returns 'L' for stores which have not bought in over a year (and are therefore Lost) and blank for all other cases. When I try the following function:

=calculate(DISTINCTCOUNT([Store]),[Lost]="L")

I get the following error:

Calculation error in measure 'Table1 1'[98cc8508-821c-4611-9777-c96938814971]: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

The original source file is structured as follows, so I cannot do it directly in the file, as Quarters are in rows and not columns:
[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"]Country[/TD]
[TD="width: 64"]Store[/TD]
[TD="width: 64"]Brand[/TD]
[TD="width: 64"]Quarter[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]a[/TD]
[TD]X[/TD]
[TD]Q1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]b[/TD]
[TD]Y[/TD]
[TD]Q1[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]c[/TD]
[TD]Y[/TD]
[TD]Q1[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]a[/TD]
[TD]Y[/TD]
[TD]Q1[/TD]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]c[/TD]
[TD]X[/TD]
[TD]Q1[/TD]
[TD="align: right"]206[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]a[/TD]
[TD]X[/TD]
[TD]Q2[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]b[/TD]
[TD]Y[/TD]
[TD]Q2[/TD]
[TD="align: right"]215[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]c[/TD]
[TD]X[/TD]
[TD]Q2[/TD]
[TD="align: right"]191[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]d[/TD]
[TD]Y[/TD]
[TD]Q2[/TD]
[TD="align: right"]197[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]e[/TD]
[TD]Z[/TD]
[TD]Q2[/TD]
[TD="align: right"]189[/TD]
[/TR]
</tbody>[/TABLE]


Any ideas?

Thanks!

 

[Art15651]

Don't make "L" a Measure. Instead, make it a new column using an IF statement and populating it with "L" or "" depending on your criteria. Then you just FILTER on the new column.

 

[KnifeandFork]

Don't make "L" a Measure. Instead, make it a new column using an IF statement and populating it with "L" or "" depending on your criteria. Then you just FILTER on the new column.

Thanks for your suggestion. The reason I haven't done this is because, as quarters are not in columns but rows, it would mean for each row referencing back to the previous quarter sales for that store, the next column two quarters back, etc... and then including yet another column with the totals. Not to mention that as each country has repeated entries for each quarter each time there's sales for every type of brand, the past values would also have to reference the brand. As I have close to 1 MM rows, this isn't really feasible.

I'd need to do something like:
[TABLE="width: 1138"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Store[/TD]
[TD]Brand[/TD]
[TD]Quarter[/TD]
[TD]Sales[/TD]
[TD]Quarter Ago & Brand Concat[/TD]
[TD]Quarter Ago Sales[/TD]
[TD]2 Quarter Ago & Brand Concat[/TD]
[TD]2 Quarter Ago Sales[/TD]
[TD]3 Quarter Ago & Brand Concat[/TD]
[TD="colspan: 2"]3 Quarter Ago Sales[/TD]
[TD]etc…[/TD]
[TD]Sum[/TD]
[TD]Lost?[/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]a[/TD]
[TD]X[/TD]
[TD]Q1[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="colspan: 4"]reference to previous brand and quarter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]b[/TD]
[TD]Y[/TD]
[TD]Q1[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]c[/TD]
[TD]Y[/TD]
[TD]Q1[/TD]
[TD="align: right"]84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]a[/TD]
[TD]Y[/TD]
[TD]Q1[/TD]
[TD="align: right"]244[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]c[/TD]
[TD]X[/TD]
[TD]Q1[/TD]
[TD="align: right"]194[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]a[/TD]
[TD]X[/TD]
[TD]Q2[/TD]
[TD="align: right"]341[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]b[/TD]
[TD]Y[/TD]
[TD]Q2[/TD]
[TD="align: right"]237[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]c[/TD]
[TD]X[/TD]
[TD]Q2[/TD]
[TD="align: right"]165[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]d[/TD]
[TD]Y[/TD]
[TD]Q2[/TD]
[TD="align: right"]298[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Armenia[/TD]
[TD]e[/TD]
[TD]Z[/TD]
[TD]Q2[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]