How to add a new column based on a condition from a separate reference table?

[kevin_oleary]

Hello! Thanks in advance for any help you can give me. I am new to Power BI and so far I am enjoying it, especially for the ease of connecting straight to our company's DB. I have multiple tables in my data, I have identified all of the relationships between one another in PowerBI. At the moment I could use help with getting the columns I need in the way that I want for the sake of reporting. I am not comfortable putting my company's data out on the internet so I'm using a couple of quick sample tables. I have a Bills Table and a Fee table:

Bills Table
Bill Number	Customer	Total
1	A	$       450.00
2	B	$       275.00
3	A	$       640.00
4	C	$       120.00
5	A	$       915.00
6	B	$       385.00
7	C	$       290.00
8	B	$       150.00

Fee Table
Bill Number	Fee Type ID	Fee Amount
1	10	$         25.90
1	15	$         64.30
2	10	$         12.99
3	19	$         16.47
3	15	$         49.56
4	12	$         27.95
6	15	$         13.29
7	12	$         84.50

I am trying to add a column to my bills table where BillsTable[BillNumber] = FeeTable[BillNumber], but I only want to bring in values from FeeTable[FeeAmount] where the [FeeTypeID] = 15. My desired output would be something like this:

Desired Result
Bill Number	Customer	Total Bill	Add'l Fees (Fee Type ID = 15)
1	A	$  450.00	$                                           64.30
2	B	$  275.00	$                                                  -
3	A	$  640.00	$                                           49.56
4	C	$  120.00	$                                                  -
5	A	$  915.00	$                                                  -
6	B	$  385.00	$                                           13.29
7	C	$  290.00	$                                                  -
8	B	$  150.00	$                                                  -

Any and all help is appreciated thank you!

[ryan_mayu · Accepted solution]

@kevin_oleary 

you can try this

Column = maxx(FILTER(Fee,'Fee'[Fee Type ID]=15&&Bill[Bill Number]=Fee[Bill Number]),'Fee'[Fee Amount])

[Anonymous · Accepted solution]

Hi @kevin_oleary ,

Thanks for the reply from @ryan_mayu  the solution is excellent. If I may, I’d like to share an additional perspective:

Consider using Power Query to merge these two tables.

let
    BillsTable = Table.FromRows(
        {
            {"1", "A", 450.00},
            {"2", "B", 275.00},
            {"3", "A", 640.00},
            {"4", "C", 120.00},
            {"5", "A", 915.00},
            {"6", "B", 385.00},
            {"7", "C", 290.00},
            {"8", "B", 150.00}
        },
        {"Bill Number", "Customer", "Total"}
    ),
    FeeTable = Table.FromRows(
        {
            {"1", "10", 25.90},
            {"1", "15", 64.30},
            {"2", "10", 12.99},
            {"3", "19", 16.47},
            {"3", "15", 49.56},
            {"4", "12", 27.95},
            {"6", "15", 13.29},
            {"7", "12", 84.50}
        },
        {"Bill Number", "Fee Type ID", "Fee Amount"}
    ),
    FilteredFeeTable = Table.SelectRows(FeeTable, each [Fee Type ID] = "15"),
    AddColumn = Table.AddColumn(
        BillsTable,
        "Fee Amount",
        each try Table.SelectRows(FilteredFeeTable, (x) => x[Bill Number] = _[Bill Number]){0}[Fee Amount] otherwise null
    )
in
    AddColumn

Best Regards,

Bof

[kevin_oleary · Accepted solution]

This is precisely what i was looking for, thank you!

[Anonymous · Accepted solution]

Hi @kevin_oleary ,

Thanks for the reply from @ryan_mayu  the solution is excellent. If I may, I’d like to share an additional perspective:

Consider using Power Query to merge these two tables.

let
    BillsTable = Table.FromRows(
        {
            {"1", "A", 450.00},
            {"2", "B", 275.00},
            {"3", "A", 640.00},
            {"4", "C", 120.00},
            {"5", "A", 915.00},
            {"6", "B", 385.00},
            {"7", "C", 290.00},
            {"8", "B", 150.00}
        },
        {"Bill Number", "Customer", "Total"}
    ),
    FeeTable = Table.FromRows(
        {
            {"1", "10", 25.90},
            {"1", "15", 64.30},
            {"2", "10", 12.99},
            {"3", "19", 16.47},
            {"3", "15", 49.56},
            {"4", "12", 27.95},
            {"6", "15", 13.29},
            {"7", "12", 84.50}
        },
        {"Bill Number", "Fee Type ID", "Fee Amount"}
    ),
    FilteredFeeTable = Table.SelectRows(FeeTable, each [Fee Type ID] = "15"),
    AddColumn = Table.AddColumn(
        BillsTable,
        "Fee Amount",
        each try Table.SelectRows(FilteredFeeTable, (x) => x[Bill Number] = _[Bill Number]){0}[Fee Amount] otherwise null
    )
in
    AddColumn

Best Regards,

Bof

[kevin_oleary · Accepted solution]

Thank you for your response! I am still learning the ins and outs of PowerBI, so I am definitely saving this for the future so I can write my own Power Queries on later problems/issues that I encounter.

[ryan_mayu · Accepted solution]

@kevin_oleary 

you can try this

Column = maxx(FILTER(Fee,'Fee'[Fee Type ID]=15&&Bill[Bill Number]=Fee[Bill Number]),'Fee'[Fee Amount])

[kevin_oleary · Accepted solution]

This is precisely what i was looking for, thank you!

[ryan_mayu]

you are welcome