Custom column using contidition statement.

[Bansi008]

Hi,

I am currently working on the validation project. I have the following 2 sample modules in the PowerBI dashboard. In Table 1, column S_Contrib_R is a custom column created using the formula below to look up using Unique Key and populate the data from Table 2.

S_Contrib_R = CALCULATE(SUM('Table 2'[CONTRIB_Master]), FILTER('Table 2', 'Table 2'[Unique Key] = Table 1[Unique Key]))

My requirement is Same column i.e S_Contrib_R needs to be modified such way using conditional statement that: -

• Condition 1: - Populate the data as per current formula as above.
• Condition 2: - Using Condition 1 if cell value is null or blank then populate the data from CONTRIB_R_MTD column for the respective Unique key.
• Condition 3: - Look for Purchase column, If Purchase = P then straight forward populate the data from CONTRIB_R_MTD column for the respective Unique Key.

Please help me with the above conditional statement where I can look up table 2 and whichever condition gets satisfied it will populate the data using Unique Key as identifier.

[danextian · Accepted solution]

Hi @Bansi008 .

Pleas try this:

formula =
VAR S_Contrib_R =
    CALCULATE (
        SUM ( 'Table 2'[CONTRIB_Master] ),
        FILTER ( 'Table 2', 'Table 2'[Unique Key] = EARLIER ( 'Table 1'[Unique Key] ) )
    )
VAR CONTRIB_R_MTD =
    CALCULATE (
        SUM ( 'Table 2'[CONTRIB_R_MTD] ),
        FILTER ( 'Table 2', 'Table 2'[Unique Key] = EARLIER ( 'Table 1'[Unique Key] ) )
    )
RETURN
    IF (
        'Table 1'[Purchase] = "P",
        CONTRIB_R_MTD,
        COALESCE ( CONTRIB_R_MTD, S_Contrib_R )
    )

If this is not what you're looking for, please post  a workable sample data (not an image) and your expected result using the same sample data.

[Bansi008]

Thanks, this perfectly works as expected.