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] )
		)
    )

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
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

    Thanks also - this solution works perfectly and also makes a better solution, and I can re-use the logic in other conversions - Brilliant!!!

    Bigglerum