Forum Discussion

lazarus1907's avatar
lazarus1907
Helper I
5 years ago
Solved

Handling tables in memory

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.

Table
variable
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

     

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      lazarus1907
      Helper I
      5 years ago

      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.

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

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

         

        union(

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