Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
You can use the below measure to get the Previous Value without having to create the summarized table:
See if this works for you
I've attached the sample PBIX file for you
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
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!
See if this works for you
I've attached the sample PBIX file for you
Proud to be a Super User!
Paul on Linkedin.
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.
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
Proud to be a Super User!
Paul on Linkedin.
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!
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
Proud to be a Super User!
Paul on Linkedin.
It works! Thank you so much!
i'm so grateful for your help! it works perfectly for me!
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.
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.
You can use the below measure to get the Previous Value without having to create the summarized table:
Thank you soooo much! It works perfectly and solves all my problems at once. Have a good day!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |