Handling tables in memory

[lazarus1907]

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.

[Fowmy]

@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

[lazarus1907]

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.

[Fowmy · Accepted solution]

@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

[lazarus1907]

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.

[amitchandak]

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

union(

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