I am working in OneBox environment where i design Power BI reports and then deploy them via VS to environment. I use Aggregated Measure and Data Entities with BOYD approach.
all nice and clean it works but I need to achieve that when a user changes entity (DATAAREAID) in the dynamics.com UI the data will load data belonging to the new selected entity.
I found a solution that is similar to what I want to achieve BUT.
this is basically Role level security where DAX expression [DATAAREAID]=username() is applied for every table that has the entity (DATAAREAID) field.
what is happening in the background is that all data is loaded into the pbi and then the dax filter is applied
this might work for some fact tables in simple star schema but if you have multiple records for the same id, for multiple entities (DARAAREAID) then when loading the data into DATA moded it will fail because the 1:Many relationships will now work anymore as there are no unique records on the 1 site of the relationship.
... so what i need is to have the filter already happening in the power query in the M not in DAX once it is loaded..
in the development, i used a parameter that I would switch between the entities in power query but if i deploy it via onebox and VS there is no way i can switch the parameter as far i know.
anyone that can help ? any idea is welome