Circular DEPENDENCY Relationship

[Anonymous]

Hi Good People PBI,

I needed to create a new table based on calculation from Table 1 My table looked like this

Table 1:

IDNO	Store_Number	Slow
AA1	S1	y
AA1	S2	n
AA1	S3	y
AA1	S4	y
AA1	S5	n
AA1	S6	y

Because of slow offloading at stores other stores get affected and hence a new coulmn needs to be created as below showing the problem causing stores .For example : S4 is affected because of slow offloading at s1 and s3 as both these storesd are marked as "y"

Table 2:

IDNO	Store_Number	Affected by
AA1	S2	S1
AA1	S3	S1
AA1	S4	S1
AA1	S4	S3
AA1	S5	S1
AA1	S5	S3
AA1	S5	S4
AA1	S6	S1
AA1	S6	S3
AA1	S6	S4

with the help of PBI community the table 2 was created with the code :
Table2 =
GENERATE (Table1,SELECTCOLUMNS (CALCULATETABLE (DISTINCT ( Table1[Store_Number] ),Table1[Index] < EARLIER(Table1[Index] ),Table1[Slow] = "y",ALLEXCEPT ( Table1, Table1[IDNO] )),"Affected by", Table1[Store_Number]))

But i am unable to establish a one to many connection from Table 1 to Table2 as the message reads a circular depenecy was detected . I have information in table 1 (such as row count for each store etc.,)that is missing in table 2 and i need to find it only by establishing this relationship. Any work around it that you guys know off? Thanks a lot

[v-lili6-msft]

hi @Anonymous 

Just try to adjust the formula as below:

Table2 = 
GENERATE (
    Table1,
    SELECTCOLUMNS (
        CALCULATETABLE (
            DISTINCT ( Table1[Store_Number] ),
            FILTER(Table1,[Index] < EARLIER ( Table1[Index] )&&
            Table1[Slow ] = "y"&&Table1[IDNO]=EARLIER(Table1[IDNO]))
        ),
        "Affected by", Table1[Store_Number]
    )
)

Regards,

Lin

[Anonymous]

Im still facing the same issue when trying to establish the relation ship 😞

P.S: slow_offloading_Stops is nothing but table 2 and [identify] is a unique ID for each record in Table 1 to be mapped as one to many relationship

[v-lili6-msft]

hi @Anonymous 

this work in my side, and here is my sample pbix file, please try it,

and if you still have the problem, please share your sample pbix file and your expected output.

Regards,

Lin

[AntrikshSharma]

@Anonymous  Can you include index column as well in Table1?

[MFelix]

Hi @Anonymous ,

Don't know what is the calculation you are trying to achieve but if you use this code as variable in a measure maybe you can calculate other values.

Has you can see below using this measure:

Measure =
VAR temp_table =
    GENERATE (
        'Table';
        SELECTCOLUMNS (
            CALCULATETABLE (
                DISTINCT ( 'Table'[Store_Number] );
                'Table'[Index] < EARLIER ( 'Table'[Index] );
                'Table'[Slow ] = "y";
                ALLEXCEPT ( 'Table'; 'Table'[IDNO] )
            );
            "Affected by"; 'Table'[Store_Number]
        )
    )
RETURN
    COUNTX ( temp_table; 'Table'[Slow ] )

I'm abble to count the Y and no of the second table altough the initial code only has  4 y and 2 no: