- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Two Calculated columns with RELATEDTABLE gives Circular Dependency
I need to find all persons that have a certain value in another table but not another value.
For instance a person that has a valid licence and also a cancellation of that licence and they are stored in a license-actions table.
I tried creating these two Calculated Columns:
Recall =
VAR _SEL = SELECTCOLUMNS ( RELATEDTABLE('LicenseStatus'), "ID", [Status] )
RETURN
IF ( "RECALL" IN _SEL, "YES", "NO" )
License =
VAR _SEL = SELECTCOLUMNS ( RELATEDTABLE('LicenseStatus'), "ID", [Status] )
RETURN
IF ( "LICENSE" IN _SEL, "YES", "NO" )
to be able to ask this question:
HaveCancelledLicense =
IF ( License = "YES" and Cancelled = "YES", "YES", "NO" )
but I get "Circular dependecy error" when I create the second Calculated Column. Why? They dont reference each other?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @hebe ,
You can try the following DAX to add calculated columns.
Issued =
VAR _SEL= CALCULATE (
COUNTROWS ( 'Status' ),
ALLEXCEPT(Person,Person[Id]),
'Status'[Status] = "Issued"
)
RETURN IF ( _SEL > 0, "Yes", "No" )
Cancelled =
VAR _SEL= CALCULATE (
COUNTROWS ( 'Status' ),
ALLEXCEPT(Person,Person[Id]),
'Status'[Status] = "Cancelled"
)
RETURN IF ( _SEL > 0, "Yes", "No" )
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
could you pls provide some sample data and expected output?
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I created a sample file to display the problem:
https://1drv.ms/u/c/9f00986c82fa1657/ES1QHM3WKKFMuq8kc5DWiwUB4BOZIRGqEfLSWLAOfobaBA?e=psTQYg
The Calcluated Columns: "Issued" and "Cancelled" in the Person table are commented out since they create a Circular Reference if they are uncommented. Why am I not allowed to have more than one Calculate Column with RELATEDTABLE in the same table?
(Yes I know this is not the optimal solution, but this is how the source database looks like and I am aware that there are other (better) ways to solve this, but I want to know why this solution doesn't work 🙂 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @hebe ,
You can try the following DAX to add calculated columns.
Issued =
VAR _SEL= CALCULATE (
COUNTROWS ( 'Status' ),
ALLEXCEPT(Person,Person[Id]),
'Status'[Status] = "Issued"
)
RETURN IF ( _SEL > 0, "Yes", "No" )
Cancelled =
VAR _SEL= CALCULATE (
COUNTROWS ( 'Status' ),
ALLEXCEPT(Person,Person[Id]),
'Status'[Status] = "Cancelled"
)
RETURN IF ( _SEL > 0, "Yes", "No" )
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thankyou for your reply uzuntasgokberk. I dont think the answer in the post you are referring to is relevant in this case. They have two measures that are obviously dependent on the result of each other and hence creates a circular dependency. In my case there is no dependency between the calculated columns so I dont understand why I get the circular reference error message.
This is all DAX. I made an error when I wrote the code for the "HaveCancelledLicense" so you are correct that it should be "&&" and not "and".
The problem seems to be that I use "RELATEDTABLE" on the same table in two separate calculated columns. I dont understand why that would not be allowed? (If that is the problem.) It is two completely separate columns and I dont see why they would effect each other but each of them work by themself but not together.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @hebe,
Circular dependecy error problem was shared before in the community. You can add column in PowerQuery.
https://community.fabric.microsoft.com/t5/Desktop/Circular-dependency/m-p/2978637#M1017963
And I don't understand the code have you create this code in PQ or DAX(new column). Because in DAX and function can't be use like this:
HaveCancelledLicense =
IF ( License = "YES" and Cancelled = "YES", "YES", "NO" )
The correct way is:
HaveCancelledLicense =
IF ( License = "YES" && Cancelled = "YES", "YES", "NO" )
Best Regards,
Gökberk Uzuntaş
LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/
Medium: https://medium.com/@uzuntasgokberk
İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
02-02-2023 01:36 PM | |||
01-09-2024 11:30 PM | |||
05-15-2024 07:24 AM | |||
01-22-2024 11:41 PM | |||
08-27-2024 12:35 PM |
User | Count |
---|---|
101 | |
74 | |
43 | |
38 | |
31 |
User | Count |
---|---|
166 | |
90 | |
65 | |
46 | |
43 |