Filter values in one table column based on values in a different table column.

[314mp_M0th4]

I have two tables, one for project costs for numerous projects and the other with cost plans for the projects. All the projects have real cost but not all projects have a cost plan. I want to either make a calculated table with all projects real costs for projects that also have a cost plan or some filter that does this in a measurement. Is this possible?

Thanks.

[v-yangliu-msft]

Hi  @314mp_M0th4 ,

I created some data:

TableA:

TableB:

Calculated table:
Table =
var _table1=
SUMMARIZE(
    'TableB',
    'TableB'[Projects],'TableB'[cost plan],"real projects",
    MINX(
        FILTER(ALL(TableA),
        'TableA'[Projects]=EARLIER('TableB'[Projects])),'TableA'[real cost]))
return
FILTER(
    _table1,[real projects]<>BLANK())

Measure.

Measure =
MINX(
    FILTER(ALL(TableA),
    'TableA'[Projects]=MAX('TableB'[Projects])),[real cost])

Best Regards,

Liu Yang

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

[amitchandak · Accepted solution]

@314mp_M0th4 , You can have measure like

calculate(sum(Table1[Values]), filter(Table1, Table1[Project] in values(Table2[Values]) ))

or use Treatas

https://docs.microsoft.com/en-us/dax/treatas-function

or Create common  dimensions

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...