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

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

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