Forum Discussion

Delphia's avatar
Delphia
Advocate II
5 years ago
Solved

Circular dependency with calculated table

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!
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
    PaulDBrown
    5 years ago

    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

13 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    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's avatar
      Delphia
      Advocate II
      5 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    PaulDBrown
    Community Champion
    5 years ago

    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