Forum Discussion
Measures breaking after switching source from DirectQuery to Import (table relationships?)
My organization has a Power BI dashboard that uses a table loading via DirectQuery. This table contains key dimensional data about our portfolio of Buildings (real estate industry). We want to switch this data source to an Import load. I created the new, imported table in PowerBI, re-created all the relationships that exited on the DirectQuery table, then deleted the DirectQuery table. While doing QA for this change, there is an obvious issue with the data tables presented in the report: the row for each Building is returning the same result. It seems that the filter conditions/relationships between tables on the Building dimension is not working correctly, and the measures are no longer filtering the data as expected.
We are using a star-schema approach, with dimensions of Building and Time interacting with fact tables like Monthly Sales. Here is the new and old relationship structures between Building and Monthly Sales:
- Old DirectQuery Building table: related to Monthly Sales data on building_id, 1:many, single direction filtering
- New Import-type Building table: related to Monthly Sales data on building_id, 1:many, single direction filtering
The core of my finding is this:
- In the old report (DirectQuery to Building table): When I put the building_ID field from Monthly Sales table into a slicer in the old report, it can filter a data table that uses Building Name from the Building table (dimension table), so filtering is working from fact to dimension.
- In the new report (Import load of Building table): When I repeat the steps from above, the slicer based on the fact table cannot filter the report which contains a dimensional field. The fact table slicer will appropriately filter the report table if it uses only building_id from the Monthly Sales fact table.
For further context, here is an example of the measure for monthly sales that is returning unexpected results;
Measure - Month Sales =
VAR Selected_Date = SELECTEDVALUE(V_RETAIL_SALES_DISTINCT_PERIOD_TO_POWER_BI[DATE])
RETURN
CALCULATE(
SUM(V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[MTH_SALES]),
FILTER(
ALLEXCEPT(
V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI,
V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[INVESTMENT_ID],
V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[TENTCAT],
V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[TENANT_NAME],
V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[Sales Type (Friendly Format)]
),
V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[DATE] = Selected_Date
)
)I started investigating if the ALLEXCEPT function was causing some of these issues, and ran into this post in the forum; Solved: ALLEXCEPT DAX function not working - Microsoft Fabric Community, which links to this helpful article about the DAX "Auto Exist" feature. I would be inclined to believe that this is causing my issue, due to the multiple related columns included in ALLEXCEPT, but this logic worked just fine in the previous version of my report, when the Building dimension was loaded via DirectQuery. I don't see why switching to import load type would cause this issue.
I am the second person on my team to try and fail to update this report, so I greatly appreciate any help you can provide on this issue.
The behavior changed because the relationship type changed. With Building on DirectQuery and the fact in a different storage mode, Building to Monthly Sales was a limited relationship, which is executed as a SQL join at query time and does not expand the fact table to include Building. ALLEXCEPT on the fact therefore did not strip the Building filter. After switching Building to Import, the relationship is strong, the fact's expanded table now includes Building, and your ALLEXCEPT clears the Building filter along with everything else not in the keep list, which is why every Building row returns the same value.
Cleanest fix is to capture the Building selection before CALCULATE and re-apply it after ALLEXCEPT clears it:
VAR SelectedBuildings = VALUES(Building[building_id]) VAR Selected_Date = SELECTEDVALUE(V_RETAIL_SALES_DISTINCT_PERIOD_TO_POWER_BI[DATE]) RETURN CALCULATE( SUM(V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[MTH_SALES]), FILTER( ALLEXCEPT( V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI, V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[INVESTMENT_ID], V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[TENTCAT], V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[TENANT_NAME], V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[Sales Type (Friendly Format)] ), V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[DATE] = Selected_Date ), KEEPFILTERS(SelectedBuildings) )The fact-to-dim slicer behavior you noticed was the same root cause. The limited relationship being executed as a join let filtering effectively go either way. With strong single-direction relationships it will not, and turning on bi-directional cross filter in a star schema usually causes more issues than it fixes.
If this helped, a thumbs up and accepting the solution would be appreciated.
Thanks,
Shai Karmani
4 Replies
The behavior changed because the relationship type changed. With Building on DirectQuery and the fact in a different storage mode, Building to Monthly Sales was a limited relationship, which is executed as a SQL join at query time and does not expand the fact table to include Building. ALLEXCEPT on the fact therefore did not strip the Building filter. After switching Building to Import, the relationship is strong, the fact's expanded table now includes Building, and your ALLEXCEPT clears the Building filter along with everything else not in the keep list, which is why every Building row returns the same value.
Cleanest fix is to capture the Building selection before CALCULATE and re-apply it after ALLEXCEPT clears it:
VAR SelectedBuildings = VALUES(Building[building_id]) VAR Selected_Date = SELECTEDVALUE(V_RETAIL_SALES_DISTINCT_PERIOD_TO_POWER_BI[DATE]) RETURN CALCULATE( SUM(V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[MTH_SALES]), FILTER( ALLEXCEPT( V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI, V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[INVESTMENT_ID], V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[TENTCAT], V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[TENANT_NAME], V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[Sales Type (Friendly Format)] ), V_RETAIL_SALES_MONTHLY_SALES_TO_POWER_BI[DATE] = Selected_Date ), KEEPFILTERS(SelectedBuildings) )The fact-to-dim slicer behavior you noticed was the same root cause. The limited relationship being executed as a join let filtering effectively go either way. With strong single-direction relationships it will not, and turning on bi-directional cross filter in a star schema usually causes more issues than it fixes.
If this helped, a thumbs up and accepting the solution would be appreciated.
Thanks,
Shai KarmaniHi,
Shai_Karmani Great explanation nails the root cause the limited vs strong relationship shift is exactly what broke the Building filter after switching to Import mode. His VAR SelectedBuildings fix is the cleanest approach as it keeps your original measure structure intact.
ATWEST One thing worth noting regardless of which fix you apply slicers in a star schema should ideally use dimension table columns rather than fact table columns.
If this response was helpful, please accept it as a solution and give kudos to support other community members
Hi ArwaAldoud, thank you for the additional information. Yes, I do generally use columns from the dim table for slicers - my comment above was just using fact table columns in the slicer while troubleshooting my issue. Still, it's always a good reminder on best practices, thank you!
Hello Shai_Karmani thank you very much for the explaination. The difference in load behavior between DirectQuery and Import makes sense to me, and explains the strange results in my model. Your suggested DAX measure changes worked perfectly in my report.
