Nice video. Using your example, if I wanted to have the sum of all orders that had been sold but not yet delivered, how would I do that? Because the CALCULATE function can only take the 1 USERELATIONSHIP function and the problem requires both relationships.
Thanks for this helpful video. I have a query.. I have 6 different date columns. I wanted to make a single Slicer which covers all dates. [It is done by calendarauto()]. And want to make one more slicer which choose which columns date should appear in the 1st date slicer
Nice to know, i always created duplicate date tables to handle this issue. Now i don't have to do this anymore. Question. Whay if i have 3 different dates? can you create 3 relationships to the date table or is 2 the max?
You can create as many relationship as you want. You just have to create different measures for each relationship. With calculation groups you could use one calculation item for each relationship, so you don't have to multiplicate the measures in the model.
but is it necessary to craete a disabled relationship in the data model for USERELATIONSHIP to be usable or can I apply this function without having to create a disabled relationship in the data model? That was not clear in the video.
The relationship must exist in the model, you cannot create a relationship in DAX. Read this article for more information: www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/
I'm having problems to use USERELATIONSHIP and FILTER functions in an CALCULATE fórmula. I have a Punch List Table and basically I want to count the number of issues where the status is equal concluded. Each issue has an open date and a concluded date and both are linked with a Date Table where the Open Date link is activated. This is the formula that doesnt works: Result = CALCULATE([Inter_Total], FILTER(PendenciasInterface,PendenciasInterface[Status]="Concluída"), USERELATIONSHIP(Calendario[Date], PendenciasInterface[Data de Conclusão])) This is the formula that works: Result = CALCULATE([Inter_Total], PendenciasInterface[Status]="Concluída", USERELATIONSHIP(Calendario[Date], PendenciasInterface[Data de Conclusão])) When I remove the RELATIONSHIP funcition from the first formula it works well. Can you help to understand where is the problem/error?
Hi, I got the following error in one of the measures when using UseRelationship: "The UseRelationship() and CrossFilter() functions may not be used when querying '...' because it is constrained by row-level security defined on '...' or related tables." Can you make a video explaining why this error happen and how to fix. Thank you.
Does UserRelationship works with many to many relationship - if active and inactive both are many to many. Secondly if active is 1:many and inactive is many:many or vice versa, can we still use UserRelationship?
This is a good point, because we were just thinking about writing an article about the topic. Most of the times, USERELATIONSHIP also disable the other active relationship, or at least this is what seems to happen. The reality is a little bit more complex, because USERELATIONSHIP in reality just changes the priority of relationship choice in case of ambiguity. Long story short: USERELATIONSHIP activates an inactive relationship If you have another active relationship that is "stronger", you might have to disable it using CROSSFILTER ( ...., NONE). For example, this is required if you activate a many-to-many cardinality relationship between two tables where you also have a one-to-many active relationship. More about that in a future article.
Thaks for sharing Alberto. I was thinking if you can explain us. How to create a consecutive ranking with RANKX. When we have repeated numbers. “Skip” or “Dense” always put the same number when scan same results. Thank you.
That is a good idea for another video. Anyway, you need to create a unique expression, combining the value you want to rank with some other column, so to make the expression unique. I'll put it in the video queue, stay tuned!
i used the same DAX you presented, but it appeared the total of all cases, Please help. ex : clo = CALCULATE(COUNTROWS('Main data'), USERELATIONSHIP('Date Table'[Date],'Main data'[Date reintegration]))
Hello, I am new to PowerBI: I have two tables - Project List: * OwnerID * SupervisorID * ManagerID - User_Info_List: * UserID * Firstname * Lastname I need to have multiple relationship from OwnerID, SupervisorID and ManagerID to UserID (from User_Info_List) and pull the First and Lastname, however, PowerBI only allowing me to have 1 Active Relationship. Is there a way for this? Thank you.
Alberto, I have read all your articles about USERELATIONSHIP and cant seem to find the answer to my business case issue. I have a measure: CALCULATE ( [Document Count], FILTER ( FACT_DOCUMENT_CONNECTIONS, FACT_DOCUMENT_CONNECTIONS[DOC_CLASS] = "CUSTINV" && FACT_DOCUMENT_CONNECTIONS[LU_NAME] IN { "IncomingInvoice", "InstantInvoice", "CustomerOrderInvHead" } ), USERELATIONSHIP ( FACT_CUSTOMER_INVOICES[Company Invoice ID], FACT_DOCUMENT_CONNECTIONS[CI_KEY] ) ) This measure is not allowing me to see the number of documents attached to a specific invoice. But when I move the relationship as a filter to a second CALCULATE() statement, then I can see the attachments. I do not understand the theory behind this one. Correct Measure: CALCULATE ( CALCULATE ( [Document Count], FILTER ( FACT_DOCUMENT_CONNECTIONS, FACT_DOCUMENT_CONNECTIONS[DOC_CLASS] = "CUSTINV" && FACT_DOCUMENT_CONNECTIONS[LU_NAME] IN { "IncomingInvoice", "InstantInvoice", "CustomerOrderInvHead" } ) ), USERELATIONSHIP ( FACT_CUSTOMER_INVOICES[Company Invoice ID], FACT_DOCUMENT_CONNECTIONS[CI_KEY] ) ) Thank you in advance.
Filter columns, not tables - by filtering a table you introduce an expanded table, with undesired side effects. Related articles: www.sqlbi.com/articles/filter-arguments-in-calculate/ www.sqlbi.com/articles/expanded-tables-in-dax/ To know more, use the book or the video course (or both): www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/ www.sqlbi.com/p/mastering-dax-video-course/
Thanks for this video!
I'm on chapter 3 of your book "The Definitive Guide to DAX" and it's been an amazing journey!
Very good illustration. Thanks :)
Nice video. Using your example, if I wanted to have the sum of all orders that had been sold but not yet delivered, how would I do that? Because the CALCULATE function can only take the 1 USERELATIONSHIP function and the problem requires both relationships.
Quick and specific, thanks
Awesome simply super
I was just looking for that method, thank you so much!
thanks for your clear explanation
Thank you for this video, it helped me a lot!
Amazing Tutorials
Nice explanation!
Thanks for this helpful video.
I have a query.. I have 6 different date columns.
I wanted to make a single Slicer which covers all dates. [It is done by calendarauto()].
And want to make one more slicer which choose which columns date should appear in the 1st date slicer
Super useful. Super thanx!
Thanks for your videos!!!!
Thank you sir. It helped :)
Many thanks
Do we need to create an in-active relationship between 2 tables even after using USERELATONSHIP DAX in a measure?
Nice to know, i always created duplicate date tables to handle this issue. Now i don't have to do this anymore. Question. Whay if i have 3 different dates? can you create 3 relationships to the date table or is 2 the max?
You can create as many relationship as you want. You just have to create different measures for each relationship. With calculation groups you could use one calculation item for each relationship, so you don't have to multiplicate the measures in the model.
but is it necessary to craete a disabled relationship in the data model for USERELATIONSHIP to be usable or can I apply this function without having to create a disabled relationship in the data model? That was not clear in the video.
The relationship must exist in the model, you cannot create a relationship in DAX. Read this article for more information: www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/
@@SQLBI thanks for the quick reply
I'm having problems to use USERELATIONSHIP and FILTER functions in an CALCULATE fórmula. I have a Punch List Table and basically I want to count the number of issues where the status is equal concluded. Each issue has an open date and a concluded date and both are linked with a Date Table where the Open Date link is activated.
This is the formula that doesnt works:
Result =
CALCULATE([Inter_Total],
FILTER(PendenciasInterface,PendenciasInterface[Status]="Concluída"),
USERELATIONSHIP(Calendario[Date], PendenciasInterface[Data de Conclusão]))
This is the formula that works:
Result =
CALCULATE([Inter_Total],
PendenciasInterface[Status]="Concluída",
USERELATIONSHIP(Calendario[Date], PendenciasInterface[Data de Conclusão]))
When I remove the RELATIONSHIP funcition from the first formula it works well.
Can you help to understand where is the problem/error?
Thank you so much
Hi,
I got the following error in one of the measures when using UseRelationship:
"The UseRelationship() and CrossFilter() functions may not be used when querying '...' because it is constrained by row-level security defined on '...' or related tables."
Can you make a video explaining why this error happen and how to fix. Thank you.
Thank you
Does UserRelationship works with many to many relationship - if active and inactive both are many to many.
Secondly if active is 1:many and inactive is many:many or vice versa, can we still use UserRelationship?
This is a good point, because we were just thinking about writing an article about the topic.
Most of the times, USERELATIONSHIP also disable the other active relationship, or at least this is what seems to happen. The reality is a little bit more complex, because USERELATIONSHIP in reality just changes the priority of relationship choice in case of ambiguity.
Long story short:
USERELATIONSHIP activates an inactive relationship
If you have another active relationship that is "stronger", you might have to disable it using CROSSFILTER ( ...., NONE). For example, this is required if you activate a many-to-many cardinality relationship between two tables where you also have a one-to-many active relationship.
More about that in a future article.
Thaks for sharing Alberto.
I was thinking if you can explain us. How to create a consecutive ranking with RANKX. When we have repeated numbers. “Skip” or “Dense” always put the same number when scan same results.
Thank you.
That is a good idea for another video. Anyway, you need to create a unique expression, combining the value you want to rank with some other column, so to make the expression unique. I'll put it in the video queue, stay tuned!
Thanks! Sir
i used the same DAX you presented, but it appeared the total of all cases, Please help. ex : clo = CALCULATE(COUNTROWS('Main data'), USERELATIONSHIP('Date Table'[Date],'Main data'[Date reintegration]))
You must apply a filter in order to see the relationship applied - the grand total includes always everything.
@@SQLBI It happened recently before it work fine. The Microsoft new update?
Hello, I am new to PowerBI:
I have two tables
- Project List:
* OwnerID
* SupervisorID
* ManagerID
- User_Info_List:
* UserID
* Firstname
* Lastname
I need to have multiple relationship from OwnerID, SupervisorID and ManagerID to UserID (from User_Info_List) and pull the First and Lastname, however, PowerBI only allowing me to have 1 Active Relationship.
Is there a way for this?
Thank you.
Hi, did you find a way? I’m also experiencing a similar problem
Great!
But the results I got here is the total of the amount column! 🙏
Alberto, I have read all your articles about USERELATIONSHIP and cant seem to find the answer to my business case issue.
I have a measure:
CALCULATE (
[Document Count],
FILTER (
FACT_DOCUMENT_CONNECTIONS,
FACT_DOCUMENT_CONNECTIONS[DOC_CLASS] = "CUSTINV"
&& FACT_DOCUMENT_CONNECTIONS[LU_NAME]
IN { "IncomingInvoice", "InstantInvoice", "CustomerOrderInvHead" }
),
USERELATIONSHIP ( FACT_CUSTOMER_INVOICES[Company Invoice ID], FACT_DOCUMENT_CONNECTIONS[CI_KEY] )
)
This measure is not allowing me to see the number of documents attached to a specific invoice. But when I move the relationship as a filter to a second CALCULATE() statement, then I can see the attachments. I do not understand the theory behind this one.
Correct Measure:
CALCULATE (
CALCULATE (
[Document Count],
FILTER (
FACT_DOCUMENT_CONNECTIONS,
FACT_DOCUMENT_CONNECTIONS[DOC_CLASS] = "CUSTINV"
&& FACT_DOCUMENT_CONNECTIONS[LU_NAME]
IN { "IncomingInvoice", "InstantInvoice", "CustomerOrderInvHead" }
)
),
USERELATIONSHIP ( FACT_CUSTOMER_INVOICES[Company Invoice ID], FACT_DOCUMENT_CONNECTIONS[CI_KEY] )
)
Thank you in advance.
Filter columns, not tables - by filtering a table you introduce an expanded table, with undesired side effects.
Related articles:
www.sqlbi.com/articles/filter-arguments-in-calculate/
www.sqlbi.com/articles/expanded-tables-in-dax/
To know more, use the book or the video course (or both):
www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/
www.sqlbi.com/p/mastering-dax-video-course/
@@SQLBI Thank you so much for the clarification. I will continue to study your related materials.