# PowerPivot - Calculate/Count Unique Row Across Multiple Tables



## excellll (Jan 2, 2013)

Hi,

My model is based on the structure below -with the fact table being the link between the peoples and country table. 

I am trying to count the number of unique People, which i have accomplished using the countrows statement. =countrows((DISTINCT(People[Name])))

My problem arises when the I try to analyse the number of unique people by Country_Name - The total number of records appear for each person and the formula does not split the total.

Is there a way for the calculated field to work across multiple tables?


*People*</SPAN>
*Fact*</SPAN>
*Country*</SPAN>
ID</SPAN>
Sales</SPAN>
ID </SPAN>
Name</SPAN>
Revenue</SPAN>
Name</SPAN>
*People*</SPAN>
*Country*</SPAN>


<TBODY>

</TBODY>

Many Thanks


----------



## Zack Barresse (Jan 2, 2013)

Hello, welcome to the board!

Basically what it sounds like is you want to count by name and by country.  If this is the case, why not just pivot the data?  Create relationships between people and country, then when pivot the data it brings it all together.  This is one of the benefits of PPV.  Why re-create the wheel?

Also, in regards to your model, doing it this way would allow a person to be shown from multiple countries.  Is that how you want it?  To me it would make sense to have all data about people in one normalized table, to include their country, or other personal data.


----------



## miguel.escobar (Jan 2, 2013)

Zack's approach is the one that I'd choose but, if that's not what you're looking for then this thread and the post from Alberto might help you

http://www.mrexcel.com/forum/powerp...ountrows-distinct-can-i-use-record-level.html


----------



## excellll (Jan 2, 2013)

Thanks for the reply.

I will have a try with the link table.

People and Country were bad examples!!!  Would have been better if i said A Linked to B and A Linked to C.  Create a measure on B and analyse the data based on data from C.

I am trying to replicate a Qlikview document within PPV, which allows measures to be analysed/filtered based on secondary and tertiary linked tables, which is what i was trying to achieve.

From what i understand, only measures on primary linked tables can be analysed (A Linked to B)


----------



## miguel.escobar (Jan 3, 2013)

you can do magic with Powerpivot and I'm pretty sure that when I understand what you're trying to achieve we'll make it right  but as of right now, I don't have a clear idea of what you're trying to achieve 
Do you think that you could perhaps upload the workbook and give us an example of how would you like the results to be shown?

Best,
Miguel


----------



## excellll (Jan 3, 2013)

Hi,

here is a link with an example

https://www.dropbox.com/s/u721u6wcii3cmbr/PPV_COUNTROWS.xlsx


----------



## miguel.escobar (Jan 3, 2013)

got it. So
 Table 1 = Fact
Other 2 tables = DIM

My question now is, what do you really want to calculate?


----------



## Siraj.Samsudeen (Jan 3, 2013)

Hi excellll,
I had a look at the attached Excel and the PivotTable built with it. What you are trying to do is to display a pivot table that has the market, the total cost of products in that market, the total revenue and distinct count of products in the market. 

Here is one way to solve it. There might be other easier ways to solve this problem that I am not aware of:

You have to establish a many-to-many table to act as the bridge between products and markets. I have created a table called MarketProduct which has the unique combination of markets and products from your Fact table. Then, I created a measure with the following formula:



> [Product Count] = calculate((DISTINCTCOUNT(product[_product_area])),'ProductMarket')



And here is a picture of the pivot table - please note that I have added a few test records and changed the values of some cost and revenues to make debugging easier. 

https://www.dropbox.com/s/mrufieq5fazkn2o/Count Rows in  many2many.png

Here is a picture of the model with the new table added to solve the many2many issue:

https://www.dropbox.com/s/5bme5pqehhiyywb/Count Rows in many2many model.png

And here is the original XL file with the changes I have done - Please note that I have renamed tables to make it meaningful for me to understand them (I can't work with table1, table2, etc.)

https://www.dropbox.com/s/lv73c3h4phkyd8x/Count Rows across different tables.xlsx

To understand how this solution works, please take a look at
Resolving Many to Many relationships leveraging DAX Cross Table Filtering « Gerhard Brueckl's BI Blog


----------



## miguel.escobar (Jan 3, 2013)

Elegant!

What I was actually gonna come up with was to actually just drag the Product field from the Fact table and just make it a distinct count (that only if what you were aiming at is what Siraj came up with).

You can make it a distinct count by just right clicking on the field once it's on the values field of the Powerpivot field list.

Great approach, Siraj!


----------



## Siraj.Samsudeen (Jan 4, 2013)

Hi Miguel, thanks a lot for your kind words!

I just wanted to understand what you are saying better as it does not produce the right results for me. In the pivot table that I have given in the first screenshot, if I remove the measure and add the _PRODUCT column from Product table and change the summarize by to DISTINCTCOUNT, then I should get the same numbers as in the pivot table with measure - is this what you are saying? 

If I do this, all I get is 8 for all the markets and the total (from what I understand, PowerPivot has to be told about the bridge table. Otherwisse, it ignores it. That is why I have created a custom measure) - please correct me if I am wrong.


----------



## excellll (Jan 2, 2013)

Hi,

My model is based on the structure below -with the fact table being the link between the peoples and country table. 

I am trying to count the number of unique People, which i have accomplished using the countrows statement. =countrows((DISTINCT(People[Name])))

My problem arises when the I try to analyse the number of unique people by Country_Name - The total number of records appear for each person and the formula does not split the total.

Is there a way for the calculated field to work across multiple tables?


*People*</SPAN>
*Fact*</SPAN>
*Country*</SPAN>
ID</SPAN>
Sales</SPAN>
ID </SPAN>
Name</SPAN>
Revenue</SPAN>
Name</SPAN>
*People*</SPAN>
*Country*</SPAN>


<TBODY>

</TBODY>

Many Thanks


----------



## miguel.escobar (Jan 4, 2013)

Hey Siraj!

That'sp retty much it, but you need to grab the _PRODUCT from the FACT table but I think that you grabbed the one from the Product table (which will create this 8 result in all of the rows). Check out the table below and the link to the file


Row LabelsSum of CostSum of RevenueDistinct Count of _PRODUCTProduct CountAAA-BBB640180044CCC-CCC30015022DDD-BBB1280360044Grand Total2220555088

<colgroup><col><col><col><col><col></colgroup><tbody>

</tbody>
https://dl.dropbox.com/u/54063091/Count%20Rows%20across%20different%20tables.xlsx

Test it out and let me know what you think

Best,
Miguel


----------



## excellll (Jan 6, 2013)

Thanks for everyone's replies - very helpful.  

I just haven't had a chance to play with PP because of installation problems.


----------



## Laurent C (Jan 7, 2013)

As pointed by Siraj, the issue is that a dimension will not filter the other by default. So, you have to explicitly tell PowerPivot it should only consider products for which there are data (for the current context).
It basically works with a CALCULATE, just as Siraj wrote, but it is not a many-to-many problem and does not require an additional table:
[Product Count] = calculate((DISTINCTCOUNT(product[_product_area])),_YourFactsTable_)


----------



## Siraj.Samsudeen (Jan 7, 2013)

miguel.escobar said:


> Hey Siraj!
> 
> That'sp retty much it, but you need to grab the _PRODUCT from the FACT table but I think that you grabbed the one from the Product table (which will create this 8 result in all of the rows). Check out the table below and the link to the file
> Test it out and let me know what you think
> ...


Hey Miguel, thanks a lot for the clarification. Yes, it does work. Any explanation on why it works when we use it from the Fact and not from the Lookup table? Here, in this case, we are interested only in count of _PRODUCT attribute from both table. What if I am interested in getting a distinct count of _PRODUCT_DEG - how would it work then?


----------



## Siraj.Samsudeen (Jan 7, 2013)

Laurent C said:


> As pointed by Siraj, the issue is that a dimension will not filter the other by default. So, you have to explicitly tell PowerPivot it should only consider products for which there are data (for the current context).
> It basically works with a CALCULATE, just as Siraj wrote, but it is not a many-to-many problem and does not require an additional table:
> [Product Count] = calculate((DISTINCTCOUNT(product[_product_area])),_YourFactsTable_)


Hi Laurent, thanks a lot for pointing out my misunderstanding of this issue. In fact, this is a fantastic solution. I was feeling that the new bridge table solution was messy; this solution is elegant and does not additional tables. Can you provide some explanation/link on how calculate works in this case?


----------



## Laurent C (Jan 7, 2013)

As a rule CALCULATE overrides the current context. However, in that case the important thing is that PowerPivot filters tables based on values contained in shared columns.

When you define a relationship from a fact table to lookup tables (dimension), you extend the fact table, virtually adding the key columns to the fact table. 

For each market, the fact table is filtered, which also applies to the Product[ID] column (in the extended fact table). CALCULATE tells PowerPivot to use these values to filter the Products table.
More on this on Jeffrey Wang's post:
MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering


----------



## miguel.escobar (Jan 7, 2013)

Siraj.Samsudeen said:


> Hey Miguel, thanks a lot for the clarification. Yes, it does work. Any explanation on why it works when we use it from the Fact and not from the Lookup table? Here, in this case, we are interested only in count of _PRODUCT attribute from both table. What if I am interested in getting a distinct count of _PRODUCT_DEG - how would it work then?



You're probably wondering...why would it work when I drag it from the fact table and not the dim table? It's quite simple really:
It's all about how Powerpivot creates these summarized values...basically what happens is this:

When you dragged the one from the PRODUCT table, powerpivot creates a measure that looks something like this:

```
=DISTINCTCOUNT('Product'[_PRODUCT])
```

While when you drag it from the fact table it looks like this:

```
=DISTINCTCOUNT('Fact'[_PRODUCT])
```

Now, as Laurent explained, you need to actually let powerpivot know under what table to actually calculate the values to show the results that you want and that's why the measure that he gave us looks more like the one that I posted from the fact table:

```
[COLOR=#574123][I][Product Count] = calculate((DISTINCTCOUNT(product[_product_area])),[/I][/COLOR][I]YourFactsTable)[/I]
```

On the filters, you can still use them and you shouldn't have any problems with them.

The best practice would be to define the measure as Laurent did instead of drag and dropping. The drag and drop it's basically the begginer approach that I try to teach new people that are "experimenting" with Powerpivot.

Hope this helps!
Miguel


----------

