Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register 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! |
thank you .ıt works
Thank you, this works! 😄
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
13 | |
12 | |
11 | |
11 |
User | Count |
---|---|
19 | |
14 | |
14 | |
11 | |
9 |