# Dimension attribute Calculation

• ### Question

• Hello,

The objective of this job is to calculate the number of Customer by entity.

As I am manipulating a huge table, I precalculate agregate on dimension.

here is my fact table :

 Entity Customer Product Sales Entity1 Customer1 Pdt1 3000 Entity1 Customer2 Pdt1 4500 Entity1 Customer3 Pdt1 6000 Entity1 Customer4 Pdt1 7500 Entity1 Customer5 Pdt1 9000 Entity2 Customer1 Pdt2 10500 Entity2 Customer2 Pdt2 12000 Entity2 Customer3 Pdt2 13500 Entity2 Customer4 Pdt2 15000 Entity2 Customer5 Pdt2 16500 Entity2 Customer6 Pdt2 18000 Entity2 Customer7 Pdt2 19500 Entity2 Customer8 Pdt2 21000 Entity1 Customer1 Pdt1 22500 Entity1 Customer2 Pdt1 24000 Entity1 Customer3 Pdt1 25500 Entity1 Customer4 Pdt1 27000 Entity1 Customer5 Pdt1 28500 Entity2 Customer1 Pdt2 30000 Entity2 Customer2 Pdt2 31500 Entity2 Customer3 Pdt2 33000 Entity2 Customer4 Pdt2 34500 Entity2 Customer5 Pdt2 36000 Entity2 Customer6 Pdt2 37500 Entity2 Customer7 Pdt2 39000 Entity2 Customer8 Pdt2 40500 Entity1 Customer1 Pdt2 42000 Entity1 Customer2 Pdt2 43500 Entity1 Customer3 Pdt2 45000 Entity1 Customer4 Pdt2 46500 Entity1 Customer5 Pdt2 48000 Entity2 Customer1 Pdt3 49500 Entity2 Customer2 Pdt3 51000 Entity2 Customer3 Pdt3 52500 Entity2 Customer4 Pdt3 54000 Entity2 Customer5 Pdt3 55500 Entity2 Customer6 Pdt3 57000 Entity2 Customer7 Pdt3 58500 Entity2 Customer8 Pdt3 60000

With Power query I calculate a count by entity

 Entity Nombre Entity1 5 Entity2 8

In power Pivot I made a ling between the two tables on the entity key.

- One base directly on the dimension.

NB Customer:=Sumx(DISTINCT(Facts[Entity]);max('Count by entity'[Nombre]))

- And the other based on the related field Added in the fact table.

NB Related Customer:=Sumx(DISTINCT(Facts[Entity]);max(Facts[Related NB]))

In the analysis cross table you can see that the result is not the same

 Étiquettes de lignes Somme de Sales NB Related Customer NB Customer Entity1 382500 5 8 Entity2 846000 8 8 Total général 1228500 16 16

My question are :

- Why are the results différents ?

- How could I do to make a calculation directly in the dimension.

- How could I calculate this Indicator on a very hudge file without external agregation (directly in memory), and with good performances ?

Jerome

Monday, August 13, 2018 2:53 PM

• Hi Jerome,

The reason that this is not working comes down to a feature called "Auto-Exists". You are seeing this issue because Entity and Related NB are in the same table Facts. Because you have  Entity and Related NB are in the same table, the engine knows that there are no possible combinations of Entity and Related NB. Effectively the engine knows what combinations of Entity and Related NB exist with each other in table Facts.

One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issue.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, August 20, 2018 9:31 AM

### All replies

• When you say "huge" how many rows are you talking about?

Have you tried just doing DISTINCTCOUNT(Facts[Customer]) for your measure and not using the aggregate table? This should perform pretty well even on hundreds of millions of rows.

Tuesday, August 14, 2018 2:05 AM
• Hi Jerome,

>>>Why are the results différents ?
If you are using Entity1 and Entity2 in your aggregate table, you will get the same results for NB Customer and NB Related Customer. For table DISTINCT(Facts[Entity]), the max('Count by entity'[Nombre]) is always 8.

>>>How could I do to make a calculation directly in the dimension.
How could I calculate this Indicator on a very hudge file without external agregation (directly in memory), and with good performances
For this two questions, I agree with Darren, you can just try DISTINCTCOUNT(Facts[Customer]) for your measure.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Tuesday, August 14, 2018 2:39 AM
• Hello Darren,

My fiels contains betwwen 1 Million and 20 millions of rows.

I tried Something close:

NB Customer Prod work:=CALCULATE(DISTINCTCOUNT(Fact[Entity]);FILTER(Fact;Fact[Source]="Prod"))

NB Customer Prod:=CALCULATE([NB Customer Prod work];ALLEXCEPT(Fact;Fact[RU0Code]))

As you see, I need to have a total by Entity to use it as a filter.

The "source" is a complement attribute of sourcing, that can have 2 value "Prod" and "Inte".

It works, but the Resulting Excel file become slow to manipulate.

Regards

Jerome

Tuesday, August 14, 2018 8:41 AM
• Hello Willson,

I am sorry but I Don't understand your explanation. "If you are using Entity1 and Entity2"

I juste want to display the result (5 and 8) of the aggregated table "Count by entity" whatever the choosed columns.

I Don't know what formula your are using for "NB Customer", but mine Don't give the same result as NB Related Customer. Even if it works with a "related column", i Don't find it is a smart solution.

Tuesday, August 14, 2018 9:26 AM
• Hi Jerome,

>>>I am sorry but I Don't understand your explanation. "If you are using Entity1 and Entity2"
Entity1 and Entity2 might come from table [Facts] or your aggregated table [Count by entity], if you are using Entity1 and Entity2 in aggregated table [Count by entity], you will get the desired results.

>>> I Don't know what formula your are using for "NB Customer", but mine Don't give the same result as NB Related Customer. Even if it works with a "related column", i Don't find it is a smart solution.
I am just copy your DAX formula for "NB Customer" and "NB Related Customer".

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Tuesday, August 14, 2018 9:39 AM
• Wilson

>>>I am sorry but I Don't understand your explanation. "If you are using Entity1 and Entity2"
Entity1 and Entity2 might come from table [Facts] or your aggregated table [Count by entity], if you are using Entity1 and Entity2 in aggregated table [Count by entity], you will get the desired results.

This formula that works with the related column woks perfectly. It give 5 and 8 as a result.

NB Related Customer:=Sumx(DISTINCT(Facts[Entity]);max(Facts[Related NB]))

This one give Always 8 as a result.

NB Customer:=Sumx(DISTINCT(Facts[Entity]);max('Count by entity'[Nombre]))

Tuesday, August 14, 2018 9:53 AM
• Hi Jerome,

>>>This one give Always 8 as a result.
Please do check the below screen shot, I am using the identical DAX formula for measure "NB Customer" and "NB Related Customer".Just using Entity1 and Entity2 in aggregated table [Count by entity].

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, August 15, 2018 2:07 AM
• Hello Willson,

I am understanding.

The difference come from the table I am using :

- If I use 'Count by entity'.Entity, The results are the same.

- If I use the Fact table, Fact.Entity, the results are différents.

Isn't it a little bit Dangerous ?

It means that the Indicator organisation become tricky.

Jerome

Monday, August 20, 2018 9:20 AM
• Hi Jerome,

The reason that this is not working comes down to a feature called "Auto-Exists". You are seeing this issue because Entity and Related NB are in the same table Facts. Because you have  Entity and Related NB are in the same table, the engine knows that there are no possible combinations of Entity and Related NB. Effectively the engine knows what combinations of Entity and Related NB exist with each other in table Facts.

One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issue.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, August 20, 2018 9:31 AM
• Thanks for the explanation.

I am still thinking that this beahavior is strange.

We can also see in our beautifull result that 8+5=16.

It is better to know it.

Jerome

Monday, August 20, 2018 9:38 AM