Hi everybody, I'm struggling with the some interaction on my dashboard, probably because of my lack of experience in Power BI DAX & Modeling. I have simplified my data model to keep only interesting tables in this case. There is a Fact Table with all the values, and many dimensions tables linked to it (like a STAR schema). In this case I only keep the Univers_dimension, Date_dimensions and Client_dimensions. I also have an external excel file which is containing CRM data.Univers_dimension and Fact_table are linked through the dimension univers_idDate_dimensions is linked to the Fact_table through the dimension date_dtClient_dimension is linked to the Fact_table through the dimension id_clientClient_dimension is linked to the CRM through the dimension id_client/id_marketingClient_dimension has an inactive linked with the Date_dimensionCRM has an inactive linked with the Date_dimension id_client values in the Client_dimension are unique.id_marketing in the CRM is not unique : one client can bet multiple times. What I would like to do: I would like to be able to slice my CRM data (stakes metric for example) by all the dimensions that I have in my star schema. Example - if my date slicer (from date_dimension) is between the 13th and 14th of December :From the CRM data, I know that the client "1111" bets 10 dollars the 14th of December. The client "2333" bets 1$ the 13th and 5$ the 14th of December => 6$ over the time range. From the links between Client_dimension>Fact_table>Univers_dimension, I know that the client "1111" has an account open in SPORT while the client "2333" is associated to POKER. So if I filter by the dimension univers_opening I should be able to see the split of the stakes (between POKER and SPORT). But it's not working at all. First, I though it was because the date_dt (Fact_table) was not in the time range selected (date_dt from Date_dimension). But even if the date slicer starts the 12th of December, nothing is happening. Here is my formula :CRM stakes = CALCULATE( SUM(crm[stakes]), USERELATIONSHIP(crm[activity_date],date_dimension[date_dt])) I attached a sample of my PBI : https://we.tl/t-xUOWuYz8NJ Thank you in advance for your help! EDIT : in some cases, it is possible to find the same id_client in the Fact_table. For those cases, only the one with the most recent date should be considered.

Hi Anonymous , I updated your sample pbix file(see attachment), please check whether it can get your expected result. CRM stakes = VAR _allseluniv = ALLSELECTED ( 'univers_dimension'[univers_opening] ) VAR _selclient = CALCULATETABLE ( VALUES ( 'fact_table'[id_client] ), FILTER ( 'univers_dimension', 'univers_dimension'[univers_opening] IN _allseluniv ) ) VAR _selmid = CALCULATETABLE ( VALUES ( 'crm'[id_marketing] ), FILTER ( 'client_dimension', 'client_dimension'[id_client] IN _selclient ) ) RETURN CALCULATE ( SUM ( crm[stakes] ), FILTER ( 'crm', 'crm'[id_marketing] IN _selmid ), USERELATIONSHIP ( crm[activity_date], date_dimension[date_dt] ) ) Best Regards

External file filtered by STAR Schema Dimensions

Forum Discussion

Related content

RLS Filtering additional dimensions

degenerate dimensions

Remove filters from a dimension

RLS on multiple dimensions

Filtering across multiple dimensions