Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bansi008
Helper I
Helper I

Custom column using contidition statement.

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.

 

Bansi008_0-1715364870936.png

 

1 ACCEPTED SOLUTION
danextian
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

2 REPLIES 2
danextian
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thanks, this perfectly works as expected. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors