Forum Discussion

Back2Basics's avatar
Back2Basics
Resolver I
5 years ago

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. 

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Back2Basics I've generally done it this way:

    New Column = LEN('Table'[Column]) - LEN(SUBSTITUTE('Table'[Column],",","")) + 1
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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