Calculate countif based on another table

[Back2Basics]

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. 

[v-shex-msft]

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]

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.

[v-shex-msft]

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]

@Back2Basics I've generally done it this way:

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