Forum Discussion
Time Intelligence Guru Needed
Chthonian wrote:
- What is the best way to set up my data model? 1 Date Table or 3 Date Tables (Ideally, I want to use just 1 date table to allow for filtering/slicing on a field using a “MMM-YYYY” column)
If you just want to use a single date slicer then using a single date table is the best approach. The main time I see multiple date tables used in a data model is in situations where people want to produce matrixes with things like Ordered Date on rows and Completed Date on columns.
- Is USERELATIONSHIP possible to use when you need to reference 3 separate date fields in the same measure?
Yes, you would simply call CALCULATE( ... USERELATIONSHIP() ) on any of the inactive relationships to evaluate an expression using the other relationships.
- Would table variables for each scenario be a better way forward to create my measures?
I'm not entirely sure what you are referring to here, but I suspect the answer is "no" as creating inactive relationships and using the CALCULATE( ... , USERELATIONSHIP(...)) pattern would be the preferred approach.
Chthonian wrote:
- What is the best way to set up my data model? 1 Date Table or 3 Date Tables (Ideally, I want to use just 1 date table to allow for filtering/slicing on a field using a “MMM-YYYY” column)
If you just want to use a single date slicer then using a single date table is the best approach. The main time I see multiple date tables used in a data model is in situations where people want to produce matrixes with things like Ordered Date on rows and Completed Date on columns.
- Is USERELATIONSHIP possible to use when you need to reference 3 separate date fields in the same measure?
Yes, you would simply call CALCULATE( ... USERELATIONSHIP() ) on any of the inactive relationships to evaluate an expression using the other relationships.
- Would table variables for each scenario be a better way forward to create my measures?
I'm not entirely sure what you are referring to here, but I suspect the answer is "no" as creating inactive relationships and using the CALCULATE( ... , USERELATIONSHIP(...)) pattern would be the preferred approach.
Hi d_gosbell ,
Thanks a million for taking the time to respond. I am struggling to get my head around the USERELATIONSHIP function for multiple dates, so please excuse my persistent questions. I am on a mission to get an understanding of this side of Time Intelligence.
So in an instance with USERELATIONSHIP, my scenario would be as follows.
Calculate Gross Profit where;
Order Date is less than the 1st Dec 2019,AND ( Completed Date is IN Dec 2019,
OR Status Date is IN Dec 2019 )I am just unsure how to structure such a calculation/formula to handle all three date columns.
Many thanks again,
David
So that specific example is too complex to do with USERELATIONSHIP. If you have relationships they will be combined using a logical AND. If you need to mix in the "less than" and OR conditions you would need to do this in a DAX expression, but the exact expression depends on your data model.
You mentioned that you would be happy to provide an example data model. If you could do that it would be much easier to give a concrete example of how to structure this sort of calculation.
