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

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

Hi,

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