March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have two tables. One is a Calendar Dimension the other has values in it. They are linked by an ID, and I need to create a 'flag' to determine whether the value row is in the past 7 days, but I can't seem to get the relationship to work. I tried using USERELATIONSHIP and RELATED and RELATEDTABLE, but maybe I'm unable to get it to work.
Calendar Dimension:
Id | Date |
1 | 01/01/2021 |
2 | 02/01/2021 |
.. | .. |
4 | 20/01/2021 |
5 | 21/01/2021 |
Values table:
Value | DateID | InPast7Days |
500 | 1 | 0 |
-400 | 2 | 0 |
900 | 5 | 1 |
What i want is to use the relationship, to add the column with the 0's if the date is not within the past 7 days, and 1 if it is within the past 7 days. Hope this makes sense, and that you can help 🙂
Solved! Go to Solution.
@C-Jac , Create new column in values table
if(related([Date]) >= today()-7 && related([Date]) <= today(),1,0)
@C-Jac , make sure a relationship is correctly established between two tables by this means: Calendar[ID] (1:*) Values[DateID]; then you might want to try adding a calculated column in Values table,
InPast7Days =
VAR __dt = RELATED ( Calendar[Date] )
RETURN
( TODAY () - 7 <= __dt && __dt <= TODAY() ) + 0
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@C-Jac , Create new column in values table
if(related([Date]) >= today()-7 && related([Date]) <= today(),1,0)
thank you .ıt works
Thank you, this works! 😄
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
25 | |
18 | |
15 | |
9 | |
8 |
User | Count |
---|---|
37 | |
32 | |
18 | |
16 | |
13 |