Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cartesian Product in table visual with simple model - not sure my understanding is correct

Hi,

 

I came across some behaviour today and would like to be able to understand and explain why - im just trying to get to grips with evaluation contexts and this feels like it could be a newbie question.  Ive looked through a couple of similar posts but neither really have an explanation E.g

 

community.powerbi.com/t5/Desktop/Measure-creates-cartesian/td-p/733085

community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-creates-cartesian/td-p/721898

 

I have a very simple model with two tables, customer, sales per customer and the two are related by a customer key

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have two measures in the model

SalesMeasure = SUM(Sales[Sales])
AllSalesMeasure = CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Sales))

I add a table to the report designer and add the following

 

Customer.Name

SalesMeasure

AllSalesMeasure

 

Producing the output

 

 

 

All good so far - but I would like to expand this into line detail, I remove the SalesMeasure and add the Sales.Sales column - the result appears to be a cartesian product but I would expect to see a line for each sale per customer, with the value of all sales on each line. 

 

 

 

 

 

 

 

 

If I add new columns to the Sales Table using the RELATED function, I can get the expected result but this seems like it should not be necessary due to the relationship so im unsure if its just me who is expecting the wrong result.

Thanks in advance

 

Henry

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , in the last screenshot you seem to have use sales as not summarized? So distinct customer id and sales will come with all sales. As [all sales] is using all it will remain the same.

    Can you explain what is wrong there

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak , thanks for your reply.

       

      Sure, Customer 1 only has sales values of 10, 11 and 12 as per the Sales Values Table earlier in the post but in the last screenshot you can see my table visual shows all sales values across all customers.  E.g customer 1 displays as having individual its individual sales values as well as the sales made by customer 2 and 3 as well.

       

      I would expect to see...

       

      Which I can only achieve by adding a new column to the sale table and getting the RELATED(Customer[Name]) value - I would expect this to be handled by the relationship but when using the name directly from the customers table I see the following

       

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    I think in your model something is missing, like a date column, to get a "line detail" .

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home

    FrankAT (Proud to be a Datanaut)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi FrankAT 

       

      Thank you for your reply.

       

      I see what you have done there but this is forcing a fix - If the dates are all the same I still get the same issue 😞