Forum Discussion
Resolver IFilter values in one table column based on values in a different table column.
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.
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-solution-8e3eccb41bda
2 Replies
- amitchandak
Super User
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-solution-8e3eccb41bda 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
