Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
datadonuts
Helper II
Helper II

SUMMARIZECOLUMNS produces an error using as a virtual table -> FOLLOW UP

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

 

 

@ERD 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

 

Screenshot 2021-04-06 224549.jpg

 

 

 

 

 

 

Anonymous
Not applicable

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.

lbendlin
Super User
Super User

This is an older article but it is worth reading and re-reading.

 

Introducing SUMMARIZECOLUMNS - SQLBI

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors