Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
@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
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |