Forum Discussion
DAx Support on cartesian table
Hi @lbendlin , Hi @v-lgarikapat ,
Thank you both for the time/patience and the support. I was able to figure out the way :).
So, Calculate + sumx + all is the key to be able to repeat over the rows te same amount in a cartesian table.
Thank you
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi Ibendlin,
Sorry on the time which I got to came back 😞 (on something wihch I need support my fault).
Let me try to explain what I´m trying to do in a different way.
I have 2 tables with data and 5 tables with unique data to do the connection between the data tables.
Then, Need to create a cartesian table as attach file Pivot Table 4 (just the combination of the 5 unique tables). There I try to Calculate Measure CC2 and Measure PC2 and they are the total Per Sender CC in Both data tables Pivot Table 3 and Pivot Table 5 (the "Should be" in green Column 7/8 in Pivot table 4).
Then having it, will be abe to do the calculations of 9/10/11/12 (being 12 the end goal and having this would have the allocation detail which I´m looking for :)).
Link of Excel file: "https://docs.google.com/spreadsheets/d/1fC8M8WFmWoRzIUx-1LQVbSrIEem-Ykro/edit?usp=drive_link&ouid=100880094106413413304&rtpof=true&sd=true"
If I was not able to explain myself in a good way just let me know.
Thank you in advance for the time and support.
Thank you for the sample data. I's a bit inconclusive. Not clear to me how measure CC1 is calculated? Is it based on Sum of Actual?
Hi Ibendlin,
The Measure CC1 is a simple Sumx from D_CC_Costs Table Column "Actual YTD". And this portion of formula is working well - either we apply the Measure via D_CC_Cost Table or via "cartesian table" (cartesian table being, add the all the combinations of the conection tables - Tables start by "MD*").
The Measure CC2 is where I´m strugling, so basically would be: Calculate + sumx + allexcept (same parameters as above with the filter being applying on D_CC_Cost_Sender CC_ and ideally in MD_CC_Costs_Sender CC_). The measure works well when we apply the measure via D_CC_Cost Table and are not able to identify the cost center and do the subtotal in the "cartesian table".
So here is where I would need your support advice,
Let me know if I was able to explain myself in a good way,
Thank you in advance
Hi Daniel___ ,
Thanks for your prompt response
Your CC1 measure is a straightforward SUMX over the D_CC_Costs table's Actual YTD column, which works well whether applied directly or through the cartesian table of MD_* combinations. The challenge with CC2 arises when trying to subtotal by sender cost center using CALCULATE + SUMX + ALLEXCEPT, especially when the measure is applied via the cartesian table and the filter context doesn’t flow properly. To resolve this, you can use SUMX(VALUES(D_CC_Costs_Sender[CC_]), CALCULATE(SUM(D_CC_Costs[Actual YTD]))) to iterate over distinct sender cost centers, or apply KEEPFILTERS(VALUES(...)) to preserve granularity. If the MD_CC_Costs_Sender table is disconnected, TREATAS(VALUES(MD_CC_Costs_Sender[CC_]), D_CC_Costs_Sender[CC_]) can simulate the relationship and ensure the filter reaches the fact table. These approaches help maintain correct subtotaling and grouping logic across both direct and cartesian contexts.
We appreciate your engagement and thank you for being an active part of the community.
Best Regards,
Lakshmi.
