Forum Discussion
Conditional merge/add custom colum with table
Ok, i think i finally understood what you meant.. I now did this:
- Combined dim_project and dim_company to one single table. In this table i made new ID keys which consisted of projectnumber for projects and company nr for companies.
- In fact table i made a new column and then used dax code to make this same id_key, so for some companies ID key was projectnumber and for other companies ID key was company number.
I now have a relationship with all values, which was what i wanted, and the refresh on the dataset takes 15 seconds instead of 15 minutes 🙂
Now i just need to find out how i can combine relationships, which can change over time.. so that the releationship is different lets say in 2020 than it is in 2021...
Hanspw wrote:
Yeah that was what i was trying to do yesterday, but i did not know the right formula 🙂 However expanding the column and then doing the if formula bases on column value seems to work.
The only thing is that the expanding column is making my datamodel take forever to update... Thats with 30000 rows of data. It takes maybe 10-15 min to update. Didnt know this function was that slow. Vlookup in excel does this in seconds on the same data 😕
Power Query Merge is one of the costliest operations as it kills both the preview and the query folding. That is why I suggested using additional dimension tables instead.
Ok thanks, good to know. Could you possible try to explain how i can use additional dimension tables instead of doing the merge? The goal would be that i need to combine those dimensions fromt he two dim tables, into a combined field which i can use in my reports.
- Company 1,2,3 i want to define into a "business unit" based on company code, fom table dim_company
- Company 4 and 5 need to be split into different "business units" bases on company number, fom table dim_project
- Business unit X would then be a combination of company 1 and 2, and some projects in company 4 and 5, while business unit Y would be a combination of company 3 and some projects in company 4 and 5.
- I want to in my reports show total profit/loss for each business units.
In excel i did this by a combination of if fomula and vlookup from the two tables.