Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Alternate solution power BI

I had a problem and got this solution. But I needed an alternate solution as well.

 

I have two tables, and both have a column "work item id". I need to find out how many of the id´s of column from table 1, are also present in table 2 (specifically to find which are absent), and I need this result in a calculated column.

 

Table 1 has around 500 numeric entries in the column "work item id", while table 2 has about 460 numeric entries in the column "work item id". I need a calculated column to find out which entries are not present in "work item id" table 2, but are present in "work item id" table 1.

 

 

The solution was,

 

var _cnt = countx(filter(Table2, Table2[ID]= Table1[ID]), Table2[ID])

return

if(isblank(_cnt), 0,1 )

 

 

My question is, can we do this same thing by using FIND function ?

  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 

    Please try

    Count =
    COALESCE (
        COUNTROWS (
            FILTER ( VALUES ( Table2[ID] ), FIND ( Table2[ID], Table1[ID],, 0 ) > 0 )
        ),
        0
    )

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous 

    please try

    Count =
    COALESCE (
        COUNTROWS ( FILTER ( VALUES ( Table2[ID] ), Table2[ID] = Table1[ID] ) ),
        0
    )
    • Anonymous's avatar
      Anonymous
      Not applicable

      I wanted a solution using the FIND function in DAX (if possible). But thanks too.

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        Anonymous 

        Seems the columns don't match 100%? Would you please clarify the need for FIND function?