Forum Discussion
Help request in creating a boolean measure using related table - pbix & dax query template provided
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.
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 )
6 Replies
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 )Thank you so much. I just started working with DAX and it is making me feel really dumb.
ajit_singh
I have been there 🙂
Always- keep the model in your mind
- Think from the context evaluation perspective
- Keep it simple
- Use variables
Good luck . . .
Fowmy
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
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 🙂
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.
