Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Creating multiple if statements in DAX

Hello folks,

I am trying to create a column which uses nested IF statements to find the value in another table. I have tried the following

Allowed = 
IF(
    'zzRawData'[Docs] = "Doc" && zzRawData[FeeSched(groups)] = "Aetna",
    LOOKUPVALUE(
        'Fee Schedule'[Aetna-MD],
        'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Aetna",
        LOOKUPVALUE(
            'Fee Schedule'[Aetna-NP],
            'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "Doc" && 'zzRawData'[FeeSched(groups)] = "BCBS",
        LOOKUPVALUE(
            'Fee Schedule'[BCBS-MD],
            'Fee Schedule'[CPT Code],'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "BCBS",
        LOOKUPVALUE(
            'Fee Schedule'[BCBS-NP],
            'Fee Schedule'[CPT Code],'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "Doc" && 'zzRawData'[FeeSched(groups)] = "Cigna",
        LOOKUPVALUE(
            'Fee Schedule'[Cigna-MD],
            'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Cigna",
        LOOKUPVALUE(
            'Fee Schedule'[Cigna-NP],
            'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "Doc" && 'zzRawData'[FeeSched(groups)] = "Healthnet",
        LOOKUPVALUE(
            'Fee Schedule'[Healthnet-MD],
            'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Heatlthnet",
        LOOKUPVALUE(
            'Fee Schedule'[Healthnet-NP],
            'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "Doc" && 'zzRawData'[FeeSched(groups)] = "Humana",
        LOOKUPVALUE(
            'Fee Schedule'[Humana-MD],
            'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Humana",
        LOOKUPVALUE(
            'Fee Schedule'[Humana-NP],
            'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] + "Doc" && 'zzRawData'[FeeSched(groups)] = "Medicare",
        LOOKUPVALUE(
        'Fee Schedule'[Medicare-MD],
        'Fee Schedule'[CPT Code],'zzRawData'[CPT Code],
    IF(
        'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Medicare",
        LOOKUPVALUE(
            'Fee Schedule'[Medicare-NP],
            'Fee Schedule'[CPT Code],'zzRawData'[CPT Code]),0)))))))))))))))))))))))

When I use this code, it appears to have no errors, but only the first "IF" statemtent displays the correct values in the comlumn. All other values are empty. Can you help me understand what I am missing here?

Thank you for your time,

Need Help

sevenhills

4 years ago

I believe your intent is

Allowed = 
IF(
    'zzRawData'[Docs] = "Doc" && zzRawData[FeeSched(groups)] = "Aetna",
    LOOKUPVALUE(
        'Fee Schedule'[Aetna-MD],
        'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]
    ),

    IF( ...

... i.e., ")" is misplaced for LOOKUPVALUE and hence always the first one gets executed.

Also, suggest you to do with SWITCH for better readability in long term

Allowed = 
SWITCH (
   TRUE (),
   'zzRawData'[Docs] = "Doc" && zzRawData[FeeSched(groups)] = "Aetna",
        LOOKUPVALUE('Fee Schedule'[Aetna-MD], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]),

   'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Aetna",
        LOOKUPVALUE('Fee Schedule'[Aetna-NP], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]),

    ...
   , BLANK () -- Default Value
)

5 Replies

sevenhills
Super User
4 years ago
I believe your intent is

Allowed = IF( 'zzRawData'[Docs] = "Doc" && zzRawData[FeeSched(groups)] = "Aetna", LOOKUPVALUE( 'Fee Schedule'[Aetna-MD], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code] ), IF( ...

... i.e., ")" is misplaced for LOOKUPVALUE and hence always the first one gets executed.

Also, suggest you to do with SWITCH for better readability in long term

Allowed = SWITCH ( TRUE (), 'zzRawData'[Docs] = "Doc" && zzRawData[FeeSched(groups)] = "Aetna", LOOKUPVALUE('Fee Schedule'[Aetna-MD], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]), 'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Aetna", LOOKUPVALUE('Fee Schedule'[Aetna-NP], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]), ... , BLANK () -- Default Value )
- Anonymous
  Not applicable
  4 years ago
  sevenhills
  
  Allowed = IF( 'zzRawData'[Docs] = "Doc" && zzRawData[FeeSched(groups)] = "Aetna", LOOKUPVALUE( 'Fee Schedule'[Aetna-MD], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code] ), IF( ...
  
  Allowed = SWITCH ( TRUE (), 'zzRawData'[Docs] = "Doc" && zzRawData[FeeSched(groups)] = "Aetna", LOOKUPVALUE('Fee Schedule'[Aetna-MD], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]), 'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Aetna", LOOKUPVALUE('Fee Schedule'[Aetna-NP], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]), ... , BLANK () -- Default Value )
  
  First off, thank you for your reply. I appreciate you helping learn more.
  I tried the SWITCH function, and I got the following error:
  "A table of multiple values was supplied where a single value was expected."
  
  When I reverted back to the old code, and changed the position of the closed parethesis, I got a different error.
  "Cannot convert value 'NP/PA' of type Text to type Number."
  
  I have the data type in that column set to text, so I don't know what it's looking for.
- Anonymous
  Not applicable
  4 years ago
  Looking at this a little more, I thought maybe some more information would be helpful.
  Within 'Fee Schedule', there is a column for CPT Code. There are also two columns for each company. In the first case, Aetna. Different values are there for each company, based on [Docs]Doc or NP/PA. [Aetna-MD] and [Aetna-NP].
  
  I need to match the [CPT Code] from 'zzRaw Data'[CPT Code] to the one in 'Fee Schedule'[CPT Code] for the right company and the right provider level (Doc vs NP)
  
  Does this help to clarify? If additional information is needed, or there are any questions, please let me know.
  - sevenhills
    Super User
    4 years ago
    FYI, It is still not clear for me whether the issue is
    
    Formula
    
    Data - duplicates handling issue
    
    both
    
    Let us first check the formula issue:
    
    I see that you have lot of combinations, better to handle in original data or in M query.
    
    But since you are doing DAX, let us work on two or three combinations first just to get the syntax right. We can then add all your conditions later i.e., Iterative approach.
    
    Allowed = SWITCH ( TRUE (), 'zzRawData'[Docs] = "Doc" && zzRawData[FeeSched(groups)] = "Aetna", LOOKUPVALUE('Fee Schedule'[Aetna-MD], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]), 'zzRawData'[Docs] = "NP/PA" && 'zzRawData'[FeeSched(groups)] = "Aetna", LOOKUPVALUE('Fee Schedule'[Aetna-NP], 'Fee Schedule'[CPT Code], 'zzRawData'[CPT Code]), 'zzRawData'[Docs] = "Doc" && 'zzRawData'[FeeSched(groups)] = "BCBS", LOOKUPVALUE('Fee Schedule'[BCBS-MD], 'Fee Schedule'[CPT Code],'zzRawData'[CPT Code]), , BLANK () -- Default Value )
    
    Let me know if this works...
    
    Note: this works when you have 1-1 (oe) 1-0 lookup. "1-0" means if no data in lookup, it gives blank.
    
    Data - duplicates handling issue i.e., what if the lookup data has multiple values. (1-many)
    
    Say, you are in the lookup table, you have data as
    
    CD1 Code 123 of Dept 1
    
    CD1 Code 123A of Dept 2
    
    when you do the lookup for CD1, it will raise an error ... it becomes data - duplicate handling issue and not the formula.
    
    This does not mean the data in lookup is wrong, this means the lookup table has multiple values. But what data you are trying to pickup and how to get there ... you can check this article to understand https://www.excelnaccess.com/dealing-with-duplicates-a-table-of-multiple-values-was-supplied-using-dax/
    
    Let me know where exactly the issue is and also post some sample de-identified data.

Anonymous

Not applicable

4 years ago

Hi Anonymous,

You can try to use the following calculate column formula if it helps: (I'm nested two switch functions to check the current table field value and lookup other table field values)

Allowed =
CALCULATE (
    SWITCH (
        'zzRawData'[Docs],
        "Doc",
            SWITCH (
                'zzRawData'[FeeSched(groups)],
                "Aetna", MAX ( 'Fee Schedule'[Aetna-MD] ),
                "BCBS", MAX ( 'Fee Schedule'[BCBS-MD] ),
                "Cigna", MAX ( 'Fee Schedule'[Cigna-MD] ),
                "Healthnet", MAX ( 'Fee Schedule'[Healthnet-MD] ),
                "Humana", MAX ( 'Fee Schedule'[Humana-MD] ),
                "Medicare", MAX ( 'Fee Schedule'[Medicare-MD] ),
                0
            ),
        "NP/PA",
            SWITCH (
                'zzRawData'[FeeSched(groups)],
                "Aetna", MAX ( 'Fee Schedule'[Aetna-NP] ),
                "BCBS", MAX ( 'Fee Schedule'[BCBS-NP] ),
                "Cigna", MAX ( 'Fee Schedule'[Cigna-NP] ),
                "Healthnet", MAX ( 'Fee Schedule'[Healthnet-NP] ),
                "Humana", MAX ( 'Fee Schedule'[Humana-NP] ),
                "Medicare", MAX ( 'Fee Schedule'[Medicare-NP] ),
                0
            )
    ),
    FILTER (
        'Fee Schedule',
        'Fee Schedule'[CPT Code] = EARLIER ( 'zzRawData'[CPT Code] )
    )
)

Regards,

Xiaoxin Sheng

Forum Discussion

Creating multiple if statements in DAX

5 Replies

Related content

Multiple IF statements in DAX

How to create an if statement with multiple conditions

Multiple nested IF statements

Creating multiple Audiences in PowerBI App

Merge statement creates duplicates - Low shuffle merge as cause?