SUMX: Count unique records in a table (ignoring blanks blank rows)

[circledchicken]

Hi PowerPivot experts,

I am beginning to learn how to use PowerPivot and this question is based on the last 'Useful' example in this post:
SUMX() – The 5-point palm, exploding fxn technique « PowerPivotPro

I have a table named Table1 that looks like this:

[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Product[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Pears[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

I have two measures defined as follows:

• Count of Stores:
  =DISTINCTCOUNT(Table1[Store])
• UniqueCombinations:
  =SUMX(DISTINCT(Table1[Product]), [Count of Stores])

In my PivotTable, the only element is the 'UniqueCombinations' measure in the Values section. Currently the value of the measure is 5 corresponding to these rows:

[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Product[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Pears[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
My question is, how do I adjust the measures to exclude any rows where all the corresponding columns are blank? I would like my UniqueCombinations measure to return 4 in this case (excluding row 6).

I know I could create a calculated column for example and then count non-blanks on that (using something like =COUNTAX( DISTINCT( Table1[CalcColumn] ), Table1[CalcColumn] ). However I would like to learn how to do this using purely measures if possible.

Many thanks for your time.

 

[Laurent C]

Agreed

 

[circledchicken]

I hope that our post encourage you to use more Powerpivot and you can be certain that this community is extremely active.

Yes, that's good to know and thank you all once again for your help!