Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Back2Basics
Resolver I
Resolver I

Calculate countif based on another table

Hi

 

I have an excel table with a column for 'outcomes'. Users are able to add several outcomes, separated by commas. 

I have created a table in PBI which is just the possible 'outcomes' from the file.

 

I'm trying to find a way to count how many rows contain each of the 'outcomes'. So if the column had 5 outcomes, separated by columns, there would be a count of 1 for each of the 5 relevant outcomes. 

4 REPLIES 4
Anonymous
Not applicable

Hi @Back2Basics,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find it more quickly.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, you should split the outcome by rows to allow each outcome to appear in its own cell.  Now create your desired visual.  You may need to write a DISTINCTCOUNT() measure.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Back2Basics,

Perhaps you can try to use the following formula to expand these values, then you can use countrows function to get the item count in your field:

Column item Count=
VAR _path =
    SUBSTITUTE (
        CONCATENATEX ( VALUES ( 'Table'[Column] ), [Column], "," ),
        ",",
        "|"
    )
VAR _length =
    PATHLENGTH ( _path )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Column", PATHITEM ( _path, [Value] )
    )
RETURN
    COUNTROWS ( _pathtable )

Regards,

Xiaoxin Sheng

Greg_Deckler
Community Champion
Community Champion

@Back2Basics I've generally done it this way:

New Column = LEN('Table'[Column]) - LEN(SUBSTITUTE('Table'[Column],",","")) + 1


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.