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 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!

 

  • Delphia's avatar
    Delphia
    Icon for Advocate II rankAdvocate II
    5 years ago

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

  • Delphia's avatar
    Delphia
    Icon for Advocate II rankAdvocate II
    5 years ago

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

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

        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!