PowerPivot - Calculate/Count Unique Row Across Multiple Tables

[excellll]

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?

[TABLE="width: 320"]
<TBODY>[TR]
[TD]People</SPAN>
[/TD]
[TD][/TD]
[TD]Fact</SPAN>
[/TD]
[TD][/TD]
[TD]Country</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID</SPAN>
[/TD]
[TD][/TD]
[TD]Sales</SPAN>
[/TD]
[TD][/TD]
[TD]ID </SPAN>
[/TD]
[/TR]
[TR]
[TD]Name</SPAN>
[/TD]
[TD][/TD]
[TD]Revenue</SPAN>
[/TD]
[TD][/TD]
[TD]Name</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]People</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Country</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Many Thanks

 

[Zack Barresse]

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]

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]

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]

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]

Hi,

here is a link with an example

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

 

[miguel.escobar]

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

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

 

[Siraj.Samsudeen]

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]

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]

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.