Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Split columns based based on lookup

Hi community,

 

I have 2 different tables. One with a organisation column, one with Code column.

Besides that i have a fact with a lot of measures.

Now i only have measures on the 'department' line of Organisation. I want to split this based upon the code field of my other table, so the measures reflect on my other lines of Organisation.

Should this be done with the lookup() function?

Help is ver much appriciated.

kind regards!

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    lbendlin Thanks for your concern about this case!
    I build a sample data myself:

    I first created a secondary column (calculated column) using this DAX:

    Dept = 
IF(
    CALCULATE(
        MAX('Table1'[Value]),
        'Table1'[Organization] = "Department" 
    ) <> BLANK(),
    1,
    0
)

    Then I use this DAX to create a measure:

    Match = 
VAR CurrentID = MAX('Table1'[ID])
VAR DeptValueExists = 
CALCULATE(
    MAX('Table1'[Dept]),
    FILTER(
        'Table1',
        'Table1'[ID] = CurrentID
    )
)
VAR CurrentOrg = MAX('Table1'[Organization])
VAR CurrentCode =
CALCULATE(
    MAX('Table2'[Code]),
    RELATEDTABLE(Table2),
    'Table2'[ID] = CurrentID
)
VAR OrgFirst = LEFT(CurrentOrg, 1)
VAR CodeFirst = LEFT(CurrentCode, 1)
RETURN
IF(
    OrgFirst = CodeFirst,
    1,
    0
)

    The final output is as below:

    But this is just my guess of your dataset based on your description, if it doesn't match your actual data situation, please provide the sample data with the expected results, thanks!

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.