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 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)

Need Help

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.

Multiple If statement, return unique values.pbix29 KB

8 Replies

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.
Multiple If statement, return unique values.pbix30 KB
- 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
    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.
    Multiple If statement, return unique values.pbix29 KB
Ashish_Mathur
Super User
5 years ago
Hi,

Share some data, describe the question and show the expected result.
- 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
    Super User
    5 years ago
    Hi,
    
    I still not not understand. May be someone else will help you.
Anonymous
Not applicable
5 years ago
Worked perfectly, thanks!