"Calculate(Values(Filter...)" returns error when looking up values based on filters

[angeloola]

Hi there!

I need to return the Facies Code to a table with measured sample values for three parameters - A, B, C.

The definitions for each facies are shown below in the Definition Table.

I was trying to create a calculated column in my sample table that returns the Facies Code from the definition table based on the measured values of A, B, and C. I was using Facies Code=CALCULATE(VALUES(FILTER....) .

I keep getting errors  ("A table of multiple values was supplied where a single value was expected"...

I've included snips of my sample table and my Facies Definitoin (lookup) table below. I'm looking for the DAX code that will give me the column with the correct facies code for each of my samples.

THANKS!!!

This is the table with the definitions for A, B, and C (min and max values) for each FaciesThis is the table of unknown samples with measured values for A, B, and C. I need to return the Facies Code in a calculated column.

[Zubair_Muhammad · Accepted solution]

@angeloola

Try with this column

Facies Label =
VAR TEMP =
    CALCULATETABLE (
        VALUES ( LookupTable[Facies] ),
        FILTER (
            LookUpTable,
            SampleTable[A] >= LookupTable[A-MIN]
                && SampleTable[A] <= LookupTable[A-MAX]
                && SampleTable[B] >= LookupTable[B-MIN]
                && SampleTable[B] <= LookupTable[B-MAX]
                && SampleTable[C] >= LookupTable[C-MIN]
                && SampleTable[C] <= LookupTable[C-MAX]
        )
    )
RETURN
    CONCATENATEX ( temp, [Facies], ", " )

[angeloola]

Thanks!! Worked like a charm! 🙂

[angeloola]

@Zubair_Muhammad

Thanks again - your solution definitely works! I'm just still not sure why the CONCATENATEX function is needed and why the actual result (column labels) works the way it does if it's supposed to concatenate the result of the variable ('temp'), [Facies], and a ",". I just don't see how that expression fits with the result. I was using the rest of your expression (Calculate(Values(Filter...) and got an error, so I'm guessing the CONCATENATE function gets around that. Can you explain to me why it works like that so I can apply it in the future?

Angela 🙂 

[Zubair_Muhammad]

@angeloola

Actually if 2 or more facies meet the criteria for lookup then CALCULATE (VALUES) will throw an error becasue output can only be a single value

with CALCULATETABLE (not calculate) ,, you get a one Column Table of all the lookupvalues that meet the criteria

CONCATENATEX then combines them

You can use CALCULATE (MIN)  or CALCULATE (MAX) as well

[angeloola]

Gotcha! Thanks for the explaination. 🙂