Hi All,
I am facing below modelling issue in POWERBI.
I have 2 tablles (SALES ACTUALS and TARGETS tables) and 2 dimension tables (STATE and DATE) . I am not able to join 2 lookup tables with ACTUALs and TARGETS table because it will form a loop.
Similar to the situation below. Article explained 2 ways but i am not very clear. please provide your inputs.
Thanks
If you have a table pattern like this, with loops, then cross filtering can create an ambiguous set of relationships. For instance, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table. A common example for this kind of pattern is TableX to be a Sales table with actuals data and for TableY to be budget data. Then, the tables in the middle are lookup tables that both tables use, such as Division or Region.
Just like with active/inactive relationships, Power BI Desktop won’t allow a relationship to be set as Both if it will create ambiguity in reports. There are several different ways you can deal with this, here are the two most common:
Delete or mark relationships as inactive to reduce ambiguity. Then you might be able to set a relationship cross filtering as Both.
Bring in a table twice (with a different name the second time) to eliminate loops. This makes the pattern of relationships like a star schema. With a star schema all of the relationships can be set to Both.
