Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiple If statement, return unique values

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...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    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.

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

Hi, Anonymous 

 

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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    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's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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.