Count Number of times a value in a column appears in another column in the same table. (DAX)

[rptmaxer]

Hello, please could I have some help solving my issue?

I have two coulmns in a table called Projects.

Both columns contain the names of some projects.

I would like a third coulmn that counts how many times a project in coulmn A appears in column B.

Thank you for any help given.

N.B. The projects table is calculated a table from another table (don't know if this has any impact).

[AlB]

@rptmaxer 

No. It should work fien with a calculated table, if you simply add a calculated column with the code above. Make sure to substitute Table3 with the actual name of your table. If it doesn'twork please share the pbix (with mock data if necessary)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[rptmaxer]

@AIB

Thank you.  I ignored intellisense and just kept typing, It works perfectly! Thank you,maybe my computer was running slow, or maybe I am!

Please could I ask a followup question?

My data now looks like this:

I have added an aditional column where 0 occurs (Group)

Project List A     Project             List B Count         Group
Project 1            Porject 1                   1
Project 2            Project 2                   2
Project 2            Project 4                   2
Project 3             Project 2                  0                      A

Is it possible to:

Add another column (Count 2) and then count how many times Projects in Project List A (excluding the project marked A) appear in the entire list of projects in Project List B?

In plain english:

Re-run the count but exclude just Project 3.

[rptmaxer]

Thank you for lookng at this AIB, but intellisense won't allow me to choose the table and column to be the variable. Is it because it's a calcualted table?

[AlB · Accepted solution]

Hi @rptmaxer 

Create a calculated column:

NewCol =
VAR currentCol1_ = Table3[Column1]
RETURN
    0
        + CALCULATE (
            COUNT ( Table3[Column2] ),
            Table3[Column2] = currentCol1_,
            ALL ( Table3 )
        )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.