Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I posted a question regarding a problem I faced with SUMMARIZECOLUMNS. I got a great solution, but unfortunatelty no answer to my followup question, so here it is again.
My original post: SUMMARIZECOLUMNS produces an error using as a virtual table
My solution
margin check total group VIRTUAL =
VAR virtualtable = SUMMARIZECOLUMNS(SalesTable[Customer],"code",[Margin check code])
VAR selectedcode = SELECTEDVALUE(MarginRange[Code])
VAR returnsum =
CALCULATE(
COUNTX(virtualtable,[Customer]),
TREATAS(virtualtable, SalesTable[Customer],MarginRange[Code]),
FILTER(virtualtable,[code] = selectedcode))
RETURN
returnsum
The GOOD solution
margin check total group VIRTUAL =
var vtab2 = ADDCOLUMNS(SUMMARIZE(SalesTable, SalesTable[Customer]), "code",[Margin check code])
VAR selectedcode = SELECTEDVALUE(MarginRange[Code])
VAR returnsum =
CALCULATE(
COUNTX(vtab2,[code]),
FILTER(vtab2,[code] = selectedcode)
)
RETURN
returnsum
And here are my questions. It would be really great to get a better understanding of the solution
1. Why you do not need the TREATAS function? As there is no relationship between the range table and the support/virtual table, I was thinking I need to build one.
2. Why the a virtual table cannot be used with FILTER (VALUES ... () )
3. Which piece of DAX maked the total work?
I really appreciate some insights to understand DAX better and why this solution works comparing to mine.
Big thanks to the community
Solved! Go to Solution.
Hi @datadonuts
This is what the measure should really look like:
[margin check total group VIRTUAL] =
VAR vSelectedCode = SELECTEDVALUE( MarginRange[Code] )
var Result =
IF( vSelectedCode,
SUMX(
VALUES( SalesTable[Customer] ),
INT( [Margin check code] = vSelectedCode )
)
)
return
Result
The above will work fine if MarginRange[Code] is a number/blank. If it's text, then you have to replace Line 4 with the following line:
IF( NOT ISBLANK( vSelectedCode ),
It'll most likely be faster (sometimes MUCH faster) than the version you've got. For 2 reasons. First, it does not use CALCULATE, explicitly (only implicitly through the measure [Margin check code]), so it does not add anything to the currently existing filters. Second, it checks if the harvested code is not blank. If it is, then there's no point doing the calculations.
If vSelectedCode is blank, then the condition under IF is false and the code does not execute. SUMX is there instead of COUNTX because I sum indicator functions, which leads to the same outcome.
1. You don't need TREATAS because the columns in ADDCOLUMNS keep their lineage to the base table, so the table you create is able to filter. Of course, the column you create, [code], is without a lineage and it doesn't filter anything. But SalesTable[Customer] does retain its association.
2. I don't understand this question.
3. ???
Your code, as much as I understand, counts the number of customes visible in the current context that have [Margin check code] equal to the selected code. This is also what my measure does but in a more succint and, hopefully, faster way. I don't really get the question "which piece of DAX makes the total work." Can you please clarify the pieces I don't understand? (see above).
Hi @datadonuts
This is what the measure should really look like:
[margin check total group VIRTUAL] =
VAR vSelectedCode = SELECTEDVALUE( MarginRange[Code] )
var Result =
IF( vSelectedCode,
SUMX(
VALUES( SalesTable[Customer] ),
INT( [Margin check code] = vSelectedCode )
)
)
return
Result
The above will work fine if MarginRange[Code] is a number/blank. If it's text, then you have to replace Line 4 with the following line:
IF( NOT ISBLANK( vSelectedCode ),
It'll most likely be faster (sometimes MUCH faster) than the version you've got. For 2 reasons. First, it does not use CALCULATE, explicitly (only implicitly through the measure [Margin check code]), so it does not add anything to the currently existing filters. Second, it checks if the harvested code is not blank. If it is, then there's no point doing the calculations.
If vSelectedCode is blank, then the condition under IF is false and the code does not execute. SUMX is there instead of COUNTX because I sum indicator functions, which leads to the same outcome.
1. You don't need TREATAS because the columns in ADDCOLUMNS keep their lineage to the base table, so the table you create is able to filter. Of course, the column you create, [code], is without a lineage and it doesn't filter anything. But SalesTable[Customer] does retain its association.
2. I don't understand this question.
3. ???
Your code, as much as I understand, counts the number of customes visible in the current context that have [Margin check code] equal to the selected code. This is also what my measure does but in a more succint and, hopefully, faster way. I don't really get the question "which piece of DAX makes the total work." Can you please clarify the pieces I don't understand? (see above).
Hi Daxter,
first, your solution works just perfectly, it even does not need a virtual or helper table, as I understand. Simply brilliant (I couldn't come up with that in a hundred years). Thanks also for the explanations.
Now I just need to figure out, how your solution works.
Question number two came in respect to the virtual table, which cannot be called with the FILTER(VALUES vtable) function. As this does not relate to your solution, lets skip that.
about #3. In the earlier solution, it also returns the total. However, when I throw your solution in my original data model it works and also returns a total.
Again, thanks for your help, guys.
Hi @datadonuts
This formula
SUMX(
VALUES( SalesTable[Customer] ),
INT( [Margin check code] = vSelectedCode )
)
works because SUMX is an iterator that goes through every item/customer in the first argument, that is VALUES( ... ), and calculates the boolean expression [Margin check code] = vSelectedCode which will return True or False. Since SUMX can't work with booleans the formula uses INT to turn True to 1 and False to 0. SUMX then sums up these indicators.
This is an older article but it is worth reading and re-reading.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |