Forum Discussion

Bigglerum's avatar
Bigglerum
Frequent Visitor
4 years ago
Solved

SUMX - Problem with return value from count operation

Hi   I have a Column with Chemical element symbols separated by ";" in each cell, eg [ H;O;Pb;Ti]   There are between 1 - 16 symbols in each cell from a list of 78 valid elements in another table...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Here's how I would count with validation using PATHCONTAINS:

    ValidatedCount =
VAR CurrPath = SUBSTITUTE ( MineralInfo[ElementList], ";", "|" )
RETURN
    COUNTROWS (
        FILTER (
			PeriodicTable,
			PATHCONTAINS ( CurrPath, PeriodicTable[Element] )
		)
    )

     

Bigglerum's avatar
Bigglerum
Frequent Visitor
4 years ago

Hi thanks for you fast reply

the first solution is the same as my original query and the second doesn't seem to fire.

 

If this helps anyone - I went for a less elegant solution but it works

=LEN(TRIM(Cell))-LEN(SUBSTITUTE(TRIM(Cell),";",""))+1

ie it works out on the ";" separator and adds 1 to the total