Multiple If statement, return unique values

[wek9294]

Hello, I'm running into this issue in my BI report.  I have three different columns which are being checked before returning a value and am using two different tables.  The result is fine if I put in one IF statement, but when I try to nest multiple IF statements, it doesn't work. I can provide more details but any ideas how to fix this?

The columns being used in the search to return a value in table 2 are:

Table 1[zip code]

Table 2[zip code]

Table 1[carrier code]

Table 2[carrier code]

Table 1[number]

The formula I have is:

if(

CALCULATE(FIRSTNONBLANK(Table 1[Rate/Mile],1),

filter(

Table 1,

Table 1[Zip code] = Table 2[Zip code] &&

Table 1[carrier code] = Table 2[carrier code]),

Table 1[number] = 1) = BLANK(),

CALCULATE(FIRSTNONBLANK(Table 1[Rate/Mile],1),

filter(

Table 1,

Table 1[Zip code] = Table 2[Zip code] &&

Table 1[carrier code] = Table 2[carrier code]),

Table 1[number] = 2)

[wek9294]

Worked perfectly, thanks!

[v-alq-msft]

Hi, @wek9294 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

You may create a calculated column as below.

Result Column = 
var zcode = [Zip Codes]
var ccode = [Carrier Codes]
var num = [Number]
var val = 
CALCULATE(
    FIRSTNONBLANKVALUE(Table1[Rate],SUM(Table1[Rate])),
    FILTER(
        Table1,
        [Zip Codes]=zcode&&
        [Carrier Codes]=ccode&&
        [Number]=num
    )
)
var n = 
CALCULATE(
    MIN(Table1[Number]),
    FILTER(
        Table1,
        [Zip Codes]=zcode&&
        [Carrier Codes]=ccode&&
        [Number]>num
    )
)
var val2 = 
CALCULATE(
    SUM(Table1[Rate]),
    FILTER(
        Table1,
        [Zip Codes]=zcode&&
        [Carrier Codes]=ccode&&
        [Number]=n
    )
)
return
IF(
    ISBLANK(val),
    val2,
    val
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wek9294]

Thanks for the formula and your help, but sorry about the double work on this one.  But my data looks like:

Table 1

Table 2

And what I want it to look like is:

I can't do a straight Lookupvalue since I'm getting the, "A table of multiple values was supplied where a single value was expected" error when I do.  There's a lot more data so this is a sample of the overall file.

[v-alq-msft · Accepted solution]

Hi, @wek9294 

Based on your description, I modified the data as below. The pbix file is attached in the end.

Table1:

Table2:

You may create a calculated column or a measure as below.

Calculated column:

Result Column = 
SUMX(
    FILTER(
        ALL(Table1),
        [Zip Codes]=EARLIER(Table2[Zip Codes])&&
        [Carrier Codes]=EARLIER(Table2[Carrier Codes])
    ),
    [Rate]
)

Measure:

Result Measure = 
SUMX(
    ADDCOLUMNS(
        Table2,
        "Result",
        SUMX(
            FILTER(
                ALL(Table1),
                [Zip Codes]=EARLIER(Table2[Zip Codes])&&
                [Carrier Codes]=EARLIER(Table2[Carrier Codes])
            ),
            [Rate]
        )
    ),
    [Result]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

In Table2, write this calculated column formula

=calculate(sum('table1'[rate]),filter('table1','table1'[Zip codes]=earlier('table2'[Zip codes])&&'table1'[Carrier codes]=earlier('table2'[Carrier codes])))

Hope this helps.

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.

[wek9294]

The zip codes are "12345 - 12345"

Carrier codes are "ABCD"

Number are values of 1 - 5

I'm trying to have the formula return a value from a "Rate" column depending on these values matching.  So if the Zip code, Carrier code, and Number all match, then return the number in the Rate column such as 1.5.  But if Number 1 is blank, then return the Rate if Number is 2. When I add an IF statement at this point to try and return values 3 through 5, I'm getting a blank result.

[Ashish_Mathur]

Hi,

I still not not understand.  May be someone else will help you.