topic Re: Handling tables in memory in DAX Commands and Tips

Handling tables in memory

lazarus1907 — Thu, 14 Jan 2021 11:16:34 GMT

Hi,

I am trying to get a new column for a table counting how many times each value appears on another table. Please see diagram below:

With normal tables, this is relatively easy, eg.

Expected result =

ADDCOLUMNS(

table1,

"found",
CALCULATE(

COUNTROWS(table2),
FILTER(table2, table1[Value] = table2[Value])
)

)

The problem is that I've written a rather large and complicated code that relies on this same operation, but where both tables are actually variables (e.g. table1 = {1,2,3,4,5,6,7,8,9}) instead of normal tables, and of couse, you can't do table1[Value] in these cases. I've tried countless approaches, and I can't get it to work.
Any ideas?
Thanks in advance.

Re: Handling tables in memory

Fowmy — Thu, 14 Jan 2021 11:35:06 GMT

lazarus1907

Create the table as follows:

ADDCOLUMNS( table1, "found", var c1 = table1[Value] return CALCULATE( COUNTROWS(table2), table2[Value] = c1 ) )

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube LinkedIn

Re: Handling tables in memory

lazarus1907 — Thu, 14 Jan 2021 12:42:09 GMT

Thank you for the reply, but Power BI does not accept table1[value] when table1 is a variable and not a normal table. That's the problem I had.

Re: Handling tables in memory

amitchandak — Fri, 15 Jan 2021 05:03:36 GMT

lazarus1907 , That ia an array, You have create table like

union(

ROW("Value", 1),
ROW("Value", 2),
ROW("Value", 3)
)

Re: Handling tables in memory

Fowmy — Fri, 15 Jan 2021 11:12:53 GMT

lazarus1907

You can acheive the expected result by creating the following table:

MyTable = VAR Tab1 = { 1, 2, 3, 4, 5, 6, 7, 8, 9 } VAR Tab2 = { 4, 5, 7 } RETURN ADDCOLUMNS ( Tab1, "Found", VAR C1 = [Value] RETURN CALCULATE ( COUNTROWS ( FILTER ( Tab2, [Value] = C1 ) ) ) + 0 )

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube LinkedIn

Re: Handling tables in memory

lazarus1907 — Fri, 15 Jan 2021 14:27:58 GMT

Genius! Thank you very much!!!
I'd swear I've tried something very very similar to what you wrote, but I obviously did something wrong -probably when to filter.