Forum Discussion

Delphia's avatar
Delphia
Icon for Advocate II rankAdvocate 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 ...
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

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

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