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:

Country	Store	Brand	Quarter	Sales
Armenia	a	X	Q1	10
Armenia	b	Y	Q1	20
Armenia	c	Y	Q1	27
Armenia	a	Y	Q1	74
Armenia	c	X	Q1	206
Armenia	a	X	Q2	60
Armenia	b	Y	Q2	215
Armenia	c	X	Q2	191
Armenia	d	Y	Q2	197
Armenia	e	Z	Q2	189

<tbody>
</tbody>

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:

Country

Store

Brand

Quarter

Sales

Quarter Ago & Brand Concat

Quarter Ago Sales

2 Quarter Ago & Brand Concat

2 Quarter Ago Sales

3 Quarter Ago & Brand Concat

3 Quarter Ago Sales

etc…

Sum

Lost?

Armenia

a

X

Q1

10

reference to previous brand and quarter

Armenia

b

Y

Q1

20

Armenia

c

Y

Q1

84

Armenia

a

Y

Q1

244

Armenia

c

X

Q1

194

Armenia

a

X

Q2

341

Armenia

b

Y

Q2

237

Armenia

c

X

Q2

165

Armenia

d

Y

Q2

298

Armenia

e

Z

Q2

30

<tbody>
</tbody>