topic Re: SUMMARIZECOLUMNS produces an error using as a virtual table in DAX Commands and Tips

SUMMARIZECOLUMNS produces an error using as a virtual table

datadonuts — Sat, 27 Mar 2021 16:53:00 GMT

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

I created a range table manually

Range table

and a support table with DAX

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

Support table

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

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

What does that mean? Whats wrong here?

Thanks for helping me out. Hightly appreciated.

pbix file here -> https://drive.google.com/file/d/1W243RaSMFPzOSGR8H84DdX-CVOQbAeuB/view?usp=sharing

mahoneypat

Re: SUMMARIZECOLUMNS produces an error using as a virtual table

ERD — Sat, 27 Mar 2021 19:38:06 GMT

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!

Re: SUMMARIZECOLUMNS produces an error using as a virtual table

datadonuts — Sat, 27 Mar 2021 20:31:54 GMT

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.

Re: SUMMARIZECOLUMNS produces an error using as a virtual table

ERD — Wed, 31 Mar 2021 11:32:56 GMT

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/highlight/true#M56

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;