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

[ajit_singh]

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]

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]

[Fowmy]

@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

[ajit_singh]

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

[Fowmy · Accepted 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 )

[ajit_singh]

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. 

[Fowmy]

@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