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.

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.