I am trying to have a branch Slicer filter the “Project Details” table in a Power BI Desktop report, but in the model, the “Branches” table doesn’t have a direct relationship with the “Project Details” table. I have a “Project Branch” table that creates a relationship between the two, but it doesn’t flow from “Project Branch” to “Project Details”, it flows the in the opposite direction, which is correct but this doesn’t solve my problem. I’ve attached an example. I am not sure if there is a data model fix or a DAX workaround?
The problem is with the way the data model was designed and therefore with the way the relationship was created. The “Project Branch” table already contained or had the information mapped of the Branches against them but the “Project Details” didn’t had the information of Branches inside them.
So it’s simple that you’ll have to use the “Merge Queries” option and add the “Branches” information inside the “Project Details” table and create the direct relationship between the “Project Details” and “Branches” table and then you’ll be able to filter the results as per the branches slicer.
Below is the screenshot of the relationship status as well as of the final results provided for the reference -
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Important Note:In your PBIX file, you had created a measure based on “Application Details” table whereas the query you had raised was for “Project Details” table and thefeore I created a measure based on the latter part and filtered the results accordingly.
My apologies, I should have mentioned that merging the tables was my initial instinct too, but that removes the uniqueness of the “Project ID” column. I need the “Project Details” “Project ID” column to remain unique because it will extend down to another level, to the “Project Branch” table for example. I see that you’ve removed that table. This is just a snippet of the real thing and can be a bit tricky!
The reason why “Project ID” column is no more unique is because against the Project ID’s column under the “Project Branch” table you’ve multiple Branches mapped against it.
If your table for “Project Branch” table itself contains duplicates (Project ID’s) or multiple Branches mapped against the same Project ID then by default it’s going to give to you the duplicates as a results, no matter what. If you want the uniqueness within your “Project Details” table for “Proeject ID” column then you got to map the Branches accordingly against them so uniqueness is maintained.
Still if you want to showcase only one Project ID against that particular Branch Name then go to the “Project Details” table. Right-click onto the “Project ID” column and select the option of “Remove Duplicates” and all the duplicate Project ID’s will be removed and again you’ll have 29 records getting reflected like it was earlier within the “Project Details” table.
You cannot expect to remove duplicates just by creating a subsidiary table or using DAX when the problem itself is within the mapping of the data inside the “Project Branch” table.
Note: If you check the Power Query (the PBIX file which I sent earlier), in that you’ll observe that I haven’t removed the “Project Branch” table rather I’ve disabled the load because if I would have removed that table then I wouldn’t have been able to use the “Merge Queries” operation. Please check the file.
As I suggested the problem is within the “Mapping of the Data” and therefore that itself will create the duplicates by default. Eventhough, if you create a set of subsidiary tables (like you had created previously) or write a complex DAX to resolve this, the issue will still crop up because the base itself is the problematic i.e Mapping.
I need the “Project Details” “Project ID” column to remain unique because it will extend down to another level, to the “Project Branch” table for example.
Firstly, your “Project Details” table is being considered as Fact table in your data model and then you want to keep your “Project ID” column as a unique one. The reason why fact tables are called fact tables is because they’ll contain the information in the form of duplicates and dimension tables are the ones that contain unique information. What you’re trying to suggest and achieve here are the “EXTREME POINTS” within the same table and data model. And want the features of dimension table within the fact table itself (which I’ve never seen before).
I’ve already provided two workarounds above and the solution with the DAX is not a feasible one because if the problem is within the model or mapping itself then DAX cannot do anything in that case. So I’m not sure what other workarounds option you’re looking for.
