cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rptmaxer
Frequent Visitor

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

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).

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @rptmaxer 

Create a calculated column:

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

 

SU18_powerbi_badge

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.

 

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

@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)

 

SU18_powerbi_badge

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
Frequent Visitor

@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
Frequent Visitor

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
Super User
Super User

Hi @rptmaxer 

Create a calculated column:

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

 

SU18_powerbi_badge

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.

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors