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

Hi guys and girls, 

 

I have the following situation, simplified from my original challange. I want to count a grouping measure to create a segmentation of customers depending on their performance (good, bad, ect.)

 

Here the data and data model

 

Screenshot 2021-03-27 170844.jpg

 

Screenshot 2021-03-27 170920.jpg

 

 

 

 

 

 

 

 

 

I created a range table manually 

Range table

Screenshot 2021-03-27 171008.jpg

 

 

 

 

 

and a support table with DAX 

 

supporttable = SUMMARIZECOLUMNS(SalesTable[Customer], "code", [Margin check code])

 

Support table

Screenshot 2021-03-27 170943.jpg

 

 
 
 
 
 
 
 
Margin Check
 
margin check total group =
VAR selectedcode = SELECTEDVALUE(MarginRange[Code])
VAR returnsum =
CALCULATE(
COUNTX(supporttable,supporttable[code]),
TREATAS(supporttable, SalesTable[Customer],MarginRange[Code]),
FILTER(VALUES(supporttable[code]),supporttable[code] = selectedcode))
RETURN
returnsum
 
This works ok and gives me the correct result
 
Screenshot 2021-03-27 171542.jpg
 
 
 
 
 
 
 
 
 
 
 
But when I try so put that together in one measure using VAR, an Error comes up.
The measure itself is correct at least from the syntax, but as soon I drag it in the table visual, I get this error.
 
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
 
 
Screenshot 2021-03-27 171905.jpg
 
 
 
 
 
 
 
 
 
 
 
 
 
What does that mean? Whats wrong here?
 
Thanks for helping me out. Hightly appreciated.
 
 
 
 
 
 
 
 
 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
ERD
Super User
Super User

Hello @datadonuts ,

SUMMARIZECOLUMNS functions has some limitations described in this article: summarizecolumns (see Remarks sections).

An alternative is to use SUMMARIZE + ADDCOLUMNS.

Here is your measure:

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 by the way, it shows correct value in totals in this case.

 

Did I answer your question? Mark my post as a solution!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

3 REPLIES 3
ERD
Super User
Super User

Hello @datadonuts ,

SUMMARIZECOLUMNS functions has some limitations described in this article: summarizecolumns (see Remarks sections).

An alternative is to use SUMMARIZE + ADDCOLUMNS.

Here is your measure:

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 by the way, it shows correct value in totals in this case.

 

Did I answer your question? Mark my post as a solution!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Thanks so much, great solution. I hope one day I can figure it out by myself in just minutes like you did, bc it took me a long, long, long time to get to my solution (I won't tell you how long 😉

 

May I kindly follow up the solution with some more question to better understand the mechanics behind that

 

1. Why it does 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. Why is that not necessary amd still works?

 

2. You also removed the VALUES in the FILTER condition. I always believed, that you need VALUES in order to create a single column for the filter to iterate through, otherwise you get the error "Multiple columns ...."

 

Finally please tell me the secret which part of your solution generates the correct total. I tweaked my solution a bit to recreate it, but only your solution shows the correct total. Seems to be another hidden miracle in DAX I still don't understand.

 

Thanks again, really appreciate your help. 

Hi, sorry for the late response.

I believe it wasn't the fanciest solution but it works (I'm also in the process of learning 🙂).
P.S. For the virtual table another code may be used with the same result in this particular case:

var vtab2 = ADDCOLUMNS(DISTINCT(SalesTable[Customer]), "code",[Margin check code])

1. selectedcode variable gets code for each description from the MarginRange table. So for each row you just count the codes that are equal to the current one.

2. If you try to use virtual table with the VALUES funtion, you'll get an error: The VALUES function expects a column reference expression or a table reference expression for argument '1'.
You can either filter the whole table (not the best approach) or a column/number of columns. I'm not sure how to restrict a virtual table to a single column in this case.

3. There is a good article on measure totals (short answer - it depends on the context):
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376/highlig... 

For your first measure you could use HASONEFILTER or HASONEVALUE function to get the correct result in total.

DAX Studio can show you what it was calculating "behind the scenes":

SET DC_KIND="AUTO";
SELECT
COUNT ( )
FROM 'Table'
WHERE
'Table'[code] IN ( null, 0 ) VAND
'Table'[code] IS NOT NULL;

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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