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!