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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Bigglerum , if these tables are connected correctly then one of these two should work

 

SUMX(
VALUES('104 Periodic Table Filter'[Element]),
CALCULATE(COUNT('201 Mineral Information'[Elements])) )

 

SUMX(
Summarize('104 Periodic Table Filter','104 Periodic Table Filter'[Element], "_1",
CALCULATE(COUNT('201 Mineral Information'[Elements])) ), [_1])

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.