excelpowerbidaxpowerpivot

How to count if your column has the same values on another column in separate related table


I wanted to count the values that has the same position on my table and with my previous related table.

What I did was

=Calculate(Countrows(Table1,Filter(Table1,Table1[Position] = Table2[Position])

1ST Table

**ID    Position**
3383669 Manager
3482615 Associate
3372059 Sr Associate
3362498 Associate
2150465 Associate

2ND Table

**ID    Position**
3383669 Director
3482615 Sr Associate
3372059 Manager
3362498 Associate
2150465 Associate

I'm expecting that the answer would be 2 as two ID has the same positions from previous to recent.

What I did was

=Calculate(Countrows(Table1,Filter(Table1,Table1[Position] = Table2[Position])

I expect that 2 as two ID has the same positions from previous to recent.

It only showed

Error This can happen when a measure formula refers to a column that contains many values without


Solution

  • you can try this

    measure =
    VAR tbl =
        ADDCOLUMNS (
            Table1,
            "check",
                IF (
                    MAXX (
                        FILTER (
                            Table2,
                            Table1[ID] = Table2[ID]
                                && Table1[Position] = Table2[Position]
                        ),
                        Table2[Position]
                    ) = Table1[Position],
                    1,
                    0
                )
        )
    RETURN
        SUMX ( tbl, [check] )
    

    enter image description here