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

 

[miguel.escobar]

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

[TABLE="width: 561"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Cost[/TD]
[TD]Sum of Revenue[/TD]
[TD]Distinct Count of _PRODUCT[/TD]
[TD]Product Count[/TD]
[/TR]
[TR]
[TD]AAA-BBB[/TD]
[TD="align: right"]640[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]CCC-CCC[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]DDD-BBB[/TD]
[TD="align: right"]1280[/TD]
[TD="align: right"]3600[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]2220[/TD]
[TD="align: right"]5550[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

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]

Thanks for everyone's replies - very helpful.

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

 

[Laurent C]

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]

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

Best,
Miguel

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]

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]

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]

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