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. 

I want to create a count of these in a new column. Where the values are validated against the list of 78 elements and then counted in the column.

I have written the expression 

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

This all works fine - but where the the count of elements exceeds 11 - it stays at 11.

The data is fine and I have changed the reference elements to another source - it is the same.

Maybe there is a simpler way to count without validation?


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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super 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.

  • 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






     

  • AlexisOlson's avatar
    AlexisOlson
    Super 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