Hello again guys
I even don’t where to look for this solution. Hopefully this is the place for it.
My fact table (Main Project) is connected to Table Dates and also to Tab Time through project id. This one is connect with Table Dates with an inactive relationship.
What do i have to learn to make this relationship active? Because i need to work on this table and with several date fields.
Here’s my model and the error it gives me when i try to set active the relationship.
If you keep the relationship inactive, you will need to use USERELATIONSHIP in your measures . The alternative is to delete the inactive relationship, and use TREATAS in your measures. Here are some links for each of these.
@Rajesh don’t know how to do it because it doesn’t let me and also because i think i need all the relations the way they are…
Do you have a different opinion?
Hey @JarrettM
Thank you for answering my questions.
Is it a kind of rule that everytime i have a inactive relationship on a specific table, every measures from that inactive table should be done with userelationship or treatas?
Well technically saying, you cannot create a relationship between “Tab Time” and “Table Dates” because the relationship already exists between “Table Dates” and “Tab Main Project” tables as well as “Tab Time” and “Tab Main Project” tables.
Now, if you try to create an active relationship, I’ll simply show the circular dependency error. Rather you can create the inactive relationship and use the USERELATIONSHIP function or can also use TREATAS function. @JarrettM has already provided the links for the reference.
Guys, you’re all awesome. Always trying to help another one. Very good.
I have a couple of questions after seen these videos:
I use Treatas to use all my dax formulas from that table (the one which has an inactive relationship with dates), right? If so, i will have to change them all now, right?
I use userelationship when i want to use a measure with each date of the same fact table, right?
USERELATIONSHIP - Use this if you have a inactive relationship between 2 tables you want to create measures on. ( In your relationship view you will see a dashed line connecting each table)
TREATAS- Use this if you don’t have any active or inactive relationship on 2 tables where you want to create measures.
Well @JarrettM has already added his points on the use of USERELATIONSHIP and TREATAS function.
Now, I would also like to add one more point that simply distinguishes these two functions by miles.
You can use TREATAS function in place of USERELATIONSHIP function but you can’t use USERELATIONSHIP function in place of TREATAS function. Below is the reason provided for the reference -
If you’ve an inactive relationship between the tables one would by default think about USERELATIONSHIP function at the first place just because we’ve already created an active relationship and secondly most of the times when we witness this type of scenario that’s the first thing that comes to our mind. But it’s absolutely alright if you use the TREATAS function in this case. Then whether we create an active or inactive relationship at the first place it will simply doesn’t matter.
But TREATAS function is mostly applicable when you want to establish a relationship between the tables and it shows that it creates an “Many-to-Many” relationship. In this case, you’ll by default have to use the TREATAS function and USERELATIONSHIP cannot help in this case.
For example, if you’ve multiple dates in your model, you can use an USERELATIONSHIP function by creating inactive relationships between Fact Table and Date Table or you can simply use TREATAS function without creating any inactive relationships further. But let’s say you want to create an relationship where in your Products Tables contain multiple Product category name as well as multiple records for the Product category name in the Fact Table so now you’ll be forced to create a relationship virtually by using TREATAS function.
And that’s why I said above, TREATAS can be used in place of USERELATIONSHIP but USERELATIONSHIP cannot be used in place of TREATAS. So that’s why we can assume that TREATAS function has an upper hand in comparison to the USERELATIONSHIP function.
Hoping you find this distinction useful when it comes to TREATAS and USERELATIONSHIP function.
Sorry guys, one last question (i promiss)
If i have only one field value to get calculations, like the time field that tell us the number of hours.
My first measure would be something like this:
T Time = CALCULATE(
SUM(‘Tab Time’[tempo]),
USERELATIONSHIP ( ‘Table Dates’[Date], ‘Tab Time’[Date] )
)
my question is: all other measures that i have to do, based on this one, T Time, i don’t have to do it with the USERELATIONSHIP function, right?
Once you create a formula using the USERELATIONSHIP or TREATAS function. You can simply use that measure in your subsequent measures using measure branching technique.
And then you’ll not be required to use functions again and again in your subsequent formulas.
EDIT: Well @Greg had already provided the answer for this. After updating my post I saw his post.
