Circular dependency in calculated columns used for slicers and measures

[leste]

Hi!

I am trying to use Power BI to visualize data about a fantasy RPG.

I have a (simplified) data model that looks like the following:

The guild creation date and guild region are computed columns that look like the following:

Guild Region = CALCULATE(
    CALCULATE(
        FIRSTNONBLANK(Details[Value], NOT(ISBLANK([Value]) || [Value] = "")),
        KEEPFILTERS(Details[Detail Name] = "Region"),
        USERELATIONSHIP(Details[Entity ID], Guilds[Guild ID])
    ),
    CROSSFILTER(Players[Player ID], Details[Entity ID], NONE)
)

The creation date column is the same but with (Details[Detail name] = "Creation Date") instead.

I have visuals and measures depending on the players<->details relationship, so I want to keep that relationship active. This is why I use CROSSFILTER and USERELATIONSHIP to disable it locally in the DAX formula for the computed columns, and enable the guild<->details relationship instead.

This works when only one of column (either region or creation date) exists. When the two columns exist, I get a circular dependency error: "Guilds[Guild Creation Date], Guilds[Guild Region], Guilds[Guild Creation Date]".

In the forums, I have seen the solution to convert the columns to measures, but I don't want that: I have another table, "Alliances" (not represented above), that are groups of guilds. I want to be able to display the list of regions in which a given alliance is present, and my solution for now is a measure in the "Alliances" table:

Region list =
VAR _RegionValues = FILTER(RELATEDTABLE(Guilds), NOT(ISBLANK([Guild Region]) || [Guild Region] = ""))
RETURN CONCATENATEX(_RegionValues, [Guild Region], UNICHAR(10))

This way, I can display a table that look like:

Alliance ID	Alliance Name	Region list
1	Forest Guardians	Lindon Rohan
2	The Baddies	Mordor
3	Merchants	Gondor Rohan

If I convert the computed columns to measures, then the measure that allows this visualization stops working.

I also have a slicer to filter the visuals based on the guilds' regions, to that if I select "Rohan", only alliances 1 and 3 will show (and other visuals work the same way), and another slicer to filter based on the guild's creation date.

Of course, none of this works as long as there is a circular dependency. Is it possible to fix the circular dependency issue without breaking the "Region list" measure and the region and date slicers, or is there another approach I can follow to be able to get similar results?

Thanks in advance!

[amitchandak]

@leste , the Player, and guide should not join in this case.

In case you want to filter player slicer or values, you can use a measure that used guides values

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

or take help from

https://learn.microsoft.com/en-us/dax/treatas-function