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 ...
Need Help
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