locked
Dimension attribute Calculation RRS feed

  • 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.

    I made two calculation:

    - 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 ? 

    Thanks in advance

    Jerome

    Monday, August 13, 2018 2:53 PM

Answers

  • Hi Jerome,

    Thanks for your response.

    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.


    http://darren.gosbell.com - please mark correct answers

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

    Thanks for your question.

    >>>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,

    Thanks for your response.

    >>>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,

    Thanks for your response.

    >>>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,

    Thanks for your response.

    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