topic Re: Help request in creating a boolean measure using related table - pbix & dax query template provided in DAX Commands and Tips

Help request in creating a boolean measure using related table - pbix & dax query template provided

ajit_singh — Thu, 11 Feb 2021 04:49:07 GMT

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.

Re: Help request in creating a boolean measure using related table - pbix & dax query template provided

Fowmy — Sat, 21 Nov 2020 12:29:14 GMT

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 🙂

YouTube LinkedIn

Re: Help request in creating a boolean measure using related table - pbix & dax query template provided

ajit_singh — Sat, 21 Nov 2020 13:21:01 GMT

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.

Re: Help request in creating a boolean measure using related table - pbix & dax query template provided

Fowmy — Sat, 21 Nov 2020 13:28:26 GMT

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 )

Re: Help request in creating a boolean measure using related table - pbix & dax query template provided

ajit_singh — Sat, 21 Nov 2020 13:35:27 GMT

Thank you so much. I just started working with DAX and it is making me feel really dumb.

Re: Help request in creating a boolean measure using related table - pbix & dax query template provided

Fowmy — Sat, 21 Nov 2020 13:40:29 GMT

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

Re: Help request in creating a boolean measure using related table - pbix & dax query template provided

ajit_singh — Sat, 21 Nov 2020 13:48:49 GMT

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]