Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear DAX experts,
pbix file location: https://github.com/business-data-analytics/powerbi/raw/main/assets/ajit-dax-modeling.pbix
"f2" table has many to one relationship with "time_date" table using common "time_date_id" key.
from time_date table, I am trying to get the date column (time_date[time_date]) and a boolean if the current date falls between the min and max date from f2 table.
In the below dax query, MEASURE time_date[IsBetweenF2TableMinMaxDates] needs to be modified so that it returns "true" only if the current context date falls between the min and max date in f2 table.
DEFINE
MEASURE time_date[IsBetweenF2TableMinMaxDates] =
VAR LastDateWithData =
CALCULATE ( MAX ( time_date[time_date] ), REMOVEFILTERS () )
VAR FirstDateVisible =
MIN ( time_date[time_date] )
VAR Result = FirstDateVisible <= LastDateWithData
RETURN
Result
EVALUATE
SUMMARIZECOLUMNS (
time_date[time_date_id],
time_date[time_date],
"IsBetweenF2TableMinMaxDates", time_date[IsBetweenF2TableMinMaxDates]
)
ORDER BY time_date[time_date]
Regards,
Ajit Singh.
Solved! Go to Solution.
@ajit_singh
Make a small change to your formula: Replace RemoveFilters() with F2
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
YouTube LinkedIn
CALCULATE ( MAX ( time_date[time_date] ), F2 )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you for the valuable suggestions.
Finally, modified the measure that works at all the time levels (Year, Qtr and Month) :
DEFINE
MEASURE time_date[IsBetweenF2TableMinMaxDates] = VAR FirstDateWithData = CALCULATE ( MIN( time_date[time_date] ), f2 ) VAR LastDateWithData = CALCULATE ( MAX( time_date[time_date] ), f2 ) VAR CurrentRowDate = MIN ( time_date[time_date] ) VAR Result = CurrentRowDate >= FirstDateWithData && CurrentRowDate <= LastDateWithData RETURN Result
EVALUATE
SUMMARIZECOLUMNS(
-- time_date[time_date_id]
--, time_date[time_date]
--time_date[time_cmon_id]
--time_date[time_cqtr_id]
time_date[time_cy_id]
, "IsBetweenF2TableMinMaxDates", time_date[IsBetweenF2TableMinMaxDates]
)
order by
-- time_date[time_date]
--time_date[time_cmon_id]
--time_date[time_cqtr_id]
time_date[time_cy_id]
@ajit_singh
I have been there 🙂
Always
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you so much. I just started working with DAX and it is making me feel really dumb.
@ajit_singh
Make a small change to your formula: Replace RemoveFilters() with F2
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
YouTube LinkedIn
CALCULATE ( MAX ( time_date[time_date] ), F2 )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Sorry, by current date, I meant the date of the current row in time_date table.
f2 table contains the data for 2007, 2008 & 2009 year. So, the output of the query should have the value true only for dates of year 2007, 2008 & 2009 year and the rest of the dates should have the measure value as false.
@ajit_singh
If you have a relationship between the date and the f2 table then there is always a filter a flow so you get the current date between min and max of f2.
What do you mean by the current date ? is it today's date?
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |