Forum Discussion
Helper ICartesian product in DAX (TREATAS) by using 0 values in combination with field parameters
Hi community
I have a report with two table visuals, visualA and visualB.
Both visuals use the same table as their source:
| ID | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 |
| AAA | 999 | 999 | 999 | 999 | 999 | 999 | 999 | 999 | 999 |
| BBB | 999 | 999 | 999 | 999 | 999 | 999 | 999 | 999 | 999 |
| CCC | 999 | 999 | 999 | 999 | 999 | 999 | 999 | 999 | 999 |
VisualA contains all columns:
VisualB contains only column ID:
I want to filter visualB when I select multiple items from visualA:
As long as I explicitly use the single columns in VisualB, everything works as expected, and the generated DAX query looks fine, with only the column ID being passed to VisualB:
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"BBB",
"AAA"}, 'Sheet1'[ID])
...
But if I'm replacing the single columns with field parameters, all columns of VisualA are passed to VisualB:
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"'Sheet1'[ID]"}, 'Parameter'[Parameter Fields])
VAR __DS0FilterTable2 =
TREATAS(
{("BBB", 999, 999, 999, 999, 999, 999, 999, 999, 999),
("AAA", 999, 999, 999, 999, 999, 999, 999, 999, 999)},
'Sheet1'[ID],
...
This is not a problem in principle. However, as soon as I have values of 0 in my data, the query increases and generates two lines for the ID concerned:
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"'Sheet1'[ID]"}, 'Parameter'[Parameter Fields])
VAR __DS0FilterTable2 =
TREATAS(
{("AAA", 999, 999, 999, 999, 999, 999, 999, 999, 999),
("BBB", 0, 999, 999, 999, 999, 999, 999, 999, 999),
("BBB", BLANK(), 999, 999, 999, 999, 999, 999, 999, 999)},
'Sheet1'[ID],
...
If I now have two IDs, each with three 0 values, and select these IDs, a DAX statement with 16 lines is generated.
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"'Sheet1'[ID]"}, 'Parameter'[Parameter Fields])
VAR __DS0FilterTable2 =
TREATAS(
{("AAA", 999, 999, 999, 999, 999, 999, 999, 999, 999),
("BBB", 0, 0, 0, 999, 999, 999, 999, 999, 999),
("CCC", 0, 0, 0, 999, 999, 999, 999, 999, 999),
("BBB", BLANK(), 0, 0, 999, 999, 999, 999, 999, 999),
("CCC", BLANK(), 0, 0, 999, 999, 999, 999, 999, 999),
("BBB", 0, BLANK(), 0, 999, 999, 999, 999, 999, 999),
("CCC", 0, BLANK(), 0, 999, 999, 999, 999, 999, 999),
("BBB", BLANK(), BLANK(), 0, 999, 999, 999, 999, 999, 999),
("CCC", BLANK(), BLANK(), 0, 999, 999, 999, 999, 999, 999),
("BBB", 0, 0, BLANK(), 999, 999, 999, 999, 999, 999),
("CCC", 0, 0, BLANK(), 999, 999, 999, 999, 999, 999),
("BBB", BLANK(), 0, BLANK(), 999, 999, 999, 999, 999, 999),
("CCC", BLANK(), 0, BLANK(), 999, 999, 999, 999, 999, 999),
("BBB", 0, BLANK(), BLANK(), 999, 999, 999, 999, 999, 999),
("CCC", 0, BLANK(), BLANK(), 999, 999, 999, 999, 999, 999),
("BBB", BLANK(), BLANK(), BLANK(), 999, 999, 999, 999, 999, 999),
("CCC", BLANK(), BLANK(), BLANK(), 999, 999, 999, 999, 999, 999)},
'Sheet1'[ID],
...
It looks as for each additional value of 0 in all combinations, a Cartesian product with BLANK() is generated, which exponentially increases the DAX statement.
If I now have 19 columns with 0 values, I'm getting an error message for VisualB:
It looks like I'm running into this limitation of 30'000 data points :
My question is: Why is a Cartesian product being generated here, and how can this be avoided?
I do not want to do without the field parameters and do not want to change or replace the 0 values either.
Appreciate any help - thx!
Best regards
PBI-Enthusiast
Unfortunately, Microsoft Support was unable to provide a specific solution or workaround. The case was classified as a development scenario, which the Break/Fix team does not cover, and I was therefore advised to reach out to a Microsoft Partner (which isn't a viable solution for me).
I understand that this limitation is achieved by the generated DAX statement. I may be wrong, but in my opinion, the generated DAX statement itself is a bug and is not comprehensible.
In the meantime, I performed additional analysis and discovered that the issue occurs not only when using field parameters, but also when working with individual / explicit columns. The behaviour appears when changing the aggregation of numeric columns from “Sum” to “Don’t summarize”.
This gave me the idea of creating explicit measures (aligned with best practices). When referencing these measures within the field parameters, the generated DAX code becomes more efficient, and I no longer encounter the previous limitations.
4 Replies
PBI-EnthusiastUnfortunately, Microsoft Support was unable to provide a specific solution or workaround. The case was classified as a development scenario, which the Break/Fix team does not cover, and I was therefore advised to reach out to a Microsoft Partner (which isn't a viable solution for me).
I understand that this limitation is achieved by the generated DAX statement. I may be wrong, but in my opinion, the generated DAX statement itself is a bug and is not comprehensible.
In the meantime, I performed additional analysis and discovered that the issue occurs not only when using field parameters, but also when working with individual / explicit columns. The behaviour appears when changing the aggregation of numeric columns from “Sum” to “Don’t summarize”.
This gave me the idea of creating explicit measures (aligned with best practices). When referencing these measures within the field parameters, the generated DAX code becomes more efficient, and I no longer encounter the previous limitations.
- lbendlin
Super User
Can confirm the issue. If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .- PBI-Enthusiast
lbendlinThank you for your confirmation.
I've just submitted a support request and will keep you informed.
v-kpoloju-msftCommunity Support
Hi PBI-Enthusiast,
Thank you for submitting the support request to Microsoft. Please keep us updated on any developments.
