Circular dependency with calculated table

[Delphia]

Hi,

I have a problem with circular dependency that I don't know how to overcome.

I have the following schema:

"Date" table is autogenerated: 

Date = CALENDARAUTO()

"Group by Date and Client" table is calculated one:

Group by Date and Client =

                                   SUMMARIZECOLUMNS (

                                                  cleaned_row_count[Date],

                                                  'table'[Client],

                                                 "Total Number of Rows",

                                                  SUM (

                                                            cleaned_row_count[Number of Rows]

                                                   )

                                       )

I use this table to group my Row Count by date and by client in order to find previous value and difference:

In my report I use a slicer from "Table" to filter by Client.

As tables "Group by Date and Client" and "Table" are not connected, my "Client" slicer doesn't work for visuals that use data from "Group by Date and Client".

When I try to connect them "Client" to "Client" I have an error:

Is there any possibility to connect them? Or may be I can use filter in any other way?

I would appreciate your help!

[gdarakji · Accepted solution]

You can use the below measure to get the Previous Value without having to create the summarized table:

Previous Value =

VAR CurrentDate = MAX(cleaned_row_count[Date])

VAR ClientID = MAX('table'[id])

VAR PreviousDate = CALCULATE(MAX(cleaned_row_count[Date]),ALL('Date'[Date]),ALL(cleaned_row_count[Date]),FILTER('Date',[Date]<CurrentDate))

VAR Result = CALCULATE(SUM(cleaned_row_count[Number of Rows]),ALL('Date'[Date]),ALL(cleaned_row_count[Date]),FILTER('Date',[Date]=PreviousDate))

RETURN Result

I couldn't understand the 'table' fields because you have an id field and then a client field. The client seems to repeat for different ids.. Anyway, if you want to work per client, you might need to replace table_id with client in the above measure

[PaulDBrown · Accepted solution]

@Delphia 

See if this works for you

I've attached the sample PBIX file for you

[PaulDBrown]

Unless there is a specific reason, the table "group by date and client" would typically be set up using a visual in the report (instead of a physical table in your model)

If you really need the table in your model, you can create virtual relationships in measures using the TREATAS Function

[Delphia]

Thank you so much for your answer. I created it because haven't found a solution how to create a measure that will show a difference of Row Count by date and by client. 

In this table I calculated Previous Value for each client and date and than calculated difference. 

If you have any idea how I can do these calculations without creating a grouping table, I would appreciate your help.

You could find my file here:  https://drive.google.com/file/d/1CGVVVNZiIJCF5ww_Z5KjHAMC_Wt9GN-3/view?usp=sharing

Thank you in advance!

[PaulDBrown · Accepted solution]

@Delphia 

See if this works for you

I've attached the sample PBIX file for you

[Delphia]

Sorry, Paul. I've just found that this measure shows not correct subtotal per client:

Measure Number of Rows should show the last value per client, per table, per date, and not summerise them. Previous Date Rows shows correct number of rows. I would appreciate your help.

[PaulDBrown]

@Delphia 

So for client 1, you wish to see the 66,137 for both [number of rows] and [previous date rows]? Or do you want to see the values as shown in the row for 5/18/2021

[Delphia]

It should be value for the last available date in table (today). So, for client 1 it should be value for 5/18/2021:

Number of Rows = 66,137,151

Previous Day Rows = 66,088,701

Thank you!

[PaulDBrown · Accepted solution]

@Delphia 

Keep the previous measures (we will be using them in the final measures.

Number of rows (measure final) =
VAR MaxDate =
    CALCULATE (
        MAX ( cleaned_row_count[Date] ),
        ALLEXCEPT ( cleaned_row_count, 'table'[Client] )
    )
VAR RowNumMax =
    CALCULATE (
        [Number of rows (measure)],
        ALLEXCEPT ( cleaned_row_count, 'table'[Client] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] = MaxDate )
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Date'[Date] ), [Number of rows (measure)],
        ISINSCOPE ( 'table'[Client] ), RowNumMax
    )

Previous Date Rows (Final) =
VAR MaxDate =
    CALCULATE (
        MAX ( cleaned_row_count[Date] ),
        ALLEXCEPT ( cleaned_row_count, 'table'[Client] )
    )
VAR RowNumMax =
    CALCULATE (
        [Previous Date rows],
        ALLEXCEPT ( cleaned_row_count, 'table'[Client] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] = MaxDate )
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Date'[Date] ), [Previous Date rows],
        ISINSCOPE ( 'table'[Client] ), RowNumMax
    )

Diff in Rows vs Previous Date = [Number of rows (measure final)] - [Previous Date Rows (Final)]

Attached is the new version of the PBIX file

[Delphia]

It works! Thank you so much!

[Delphia]

i'm so grateful for your help! it works perfectly for me!

[daxer-almighty]

Please share a file that demonstrates the issue. You can place a link to a file stored on some shared drive (Google Drive, OneDrive, Dropbox...). Please remember to give us R/W permissions.

[Delphia]

Thank you for your desire to help. You could find the file here: https://drive.google.com/file/d/1CGVVVNZiIJCF5ww_Z5KjHAMC_Wt9GN-3/view?usp=sharing

I tried to solve it by creating new table 'Client' (client_name, client_id).

When I connect it to 'Table' my filters on visual don't work... And once more I have a circular dependency while connecting to table 'Group by Date and Client'.

I would appreciate your help! Let me know if you need any more information.

[gdarakji · Accepted solution]

You can use the below measure to get the Previous Value without having to create the summarized table:

Previous Value =

VAR CurrentDate = MAX(cleaned_row_count[Date])

VAR ClientID = MAX('table'[id])

VAR PreviousDate = CALCULATE(MAX(cleaned_row_count[Date]),ALL('Date'[Date]),ALL(cleaned_row_count[Date]),FILTER('Date',[Date]<CurrentDate))

VAR Result = CALCULATE(SUM(cleaned_row_count[Number of Rows]),ALL('Date'[Date]),ALL(cleaned_row_count[Date]),FILTER('Date',[Date]=PreviousDate))

RETURN Result

I couldn't understand the 'table' fields because you have an id field and then a client field. The client seems to repeat for different ids.. Anyway, if you want to work per client, you might need to replace table_id with client in the above measure

[Delphia]

Thank you soooo much! It works perfectly and solves all my problems at once. Have a good day!