Circular dependency when creating relationship with UNION table

[SMcCullagh]

Hi Guys,

I am trying to collate data from a few tables into one using UNION and SUMMARIZE functions.

Table 1 - SSLIPC

Table 2 - Nordbord

Strength Profile Table = 

UNION(

    SUMMARIZE(FILTER('SSLIPC', SSLIPC[Peak Vertical Force [N/kg]]] <> BLANK()), 'SSLIPC'[Name], 'SSLIPC'[Date], "Metric", "SSLIPC (N/kg)","Value", MAX('SSLIPC'[Peak Vertical Force [N/kg]]])),

    SUMMARIZE(FILTER(Nordbord, Nordbord[Reps L] = 3 && Nordbord[Reps R] = 3 && Nordbord[Average Force (N/kg)] <> BLANK()), Nordbord[Name], Nordbord[Date], "Metric", "Nordic (N/kg)", "Value", MAX(Nordbord[Average Force (N/kg)])))

Within the SSLIPC table I have a calculated column, 'SSLIPC'[Peak Vertical Force [N/kg]]], which looks like;

VAR combined = SSLIPC[Peak Vertical Force (Left) [N]]] + SSLIPC[Peak Vertical Force (Right) [N]]]

VAR bw = IF(ISBLANK([BW (kg)]) && ISBLANK([RA 7 Day BW (kg)]), [Season AVG BW (kg)], IF([BW (kg)] <> BLANK(), [BW (kg)], [RA 7 Day BW (kg)]))

RETURN

DIVIDE(combined,bw)

[RA 7 Day BW (kg)] & [Season AVG BW (kg)] are measures that use the calendar table within the calculations.

The issue arises when I try to create a relationship from 'date' in my 'Strength Profile Table' to 'Date' in my Calendar table.

Having read some articles around the issue, I have tried inserting ALLNOBLANKROW into FILTER of first SUMMARIZE argument.

However, I am not sure if I am using correctly.

Any help would be greatly appreciated.

Sean

[manvishah17]

Hi @SMcCullagh , 
I am looking upto your matter, see can you just change the modelling like , keep your Union table between date, player  and nordboard , sslipc.
Like your date and player filter union table and then union filter nordboard , sslipc.
Hope it helps. If you can your Pbix file only having layout option then it will be more helpful.

[manvishah17]

Do your union table named as Strength Profile Table is in relationship with 'SSLIPC' and 'Nordbord' tables? 
if yes it may be the reason of circular dependency.
If not try to generate relationship from  'Strength Profile Table'[Date] to 'Dates'[Date] n vice versa as sometimes in happens that from  tbl A to B it show circular dependancy n from B to A it allows to create relationship.
If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[SMcCullagh]

There are no relationships between 'SSLIPC' or 'Nordbord' and the UNION 'Strength Profile Table'.

When I create a relationship either way 

'Strength Profile Table'[Date] to 'Dates'[Date]

or

'Dates'[Date] to Strength Profile Table'[Date]

this creates a circular dependency.

Is there a way to use DAX within the UNION or SUMMARIZE functions that will help solve this issue?

[manvishah17]

hi @SMcCullagh ,
please try to maintain the relationship btw players table and dates. Is the both relationships is required??
now can you please ellaborate from which table you are doingg union and it showing circular dependancy.

[SMcCullagh]

'Strength Profile Table' is my UNION table from both the 'SSLIPC' and 'Nordbord' tables.

I require both relationships from 'Players' and 'Dates' tables to my 'Strength Profile Table'.

When trying to create relationship from 'Strength Profile Table'[Date] to 'Dates'[Date] I am getting the circular dependency error.

[manvishah17]

hi @SMcCullagh , 
A circular dependency is a condition that creates a cycle of filtering direction across all tables in the model. For instance, table A filters table B, table B filters table C, and table C filters table A. Please try to remove this circular dependency, or alternatively, provide an image of your model view so that I can offer further assistance

[SMcCullagh]

Hi @manvishah17 ,

Thanks for your quick reply. Please see the data model below.