How can I define a dynamic measure that part of its formula I relate to single row column?

[Anonymous]

Hi,
I have to measues:
1. periodFromDate

2. periodToDate

both a well defined and relate/react to Slicer changes. 

Now, I am trying to defined a new measure, that will create dynamic value per row:

• each row has a date
• while the date at the row is between fromDate and ToDate I need the result 1. Else, the result should be 0.

I defined the new measue as:
isInRange = 

var fromDate = [periodFromDate]
var toDate = [periodToDate]
var rowDate = selectedvalue('table'[rowDate])

return if (rowDate >= fromDate && rowDate < toDate , 1 , 0 )

It doesn't work... while I check the value of the isInRange measure per row, it is always 1, regardless the it is in dates range or out of dates range.

I would be happy to have solution for my issue.
Roy

[v-sdhruv]

Hi @Anonymous ,

Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank you for using Microsoft Community Forum

[v-sdhruv]

Hi @RoyAsh ,
Just wanted to check if you got a chance to review the suggestion provided and whether that helped you resolve your query?

Thank You

[v-sdhruv]

Hi @Anonymous ,
Just wanted to check if you got a chance to review the suggestion provided and whether that helped you resolve your query?

Thank You

[v-sdhruv · Accepted solution]

Hi @Anonymous ,

Apologies, that I misunderstood your scenario.
You can try using a calculated column-

IsInRange =
VAR fromDate = MIN([periodFromDate])
VAR toDate = MAX([periodToDate])
RETURN
IF (
'Service Request - Last Half Year'[rowDate] >= fromDate &&
'Service Request - Last Half Year'[rowDate] <= toDate,
1,
0
)

 
Calculated columns are evaluated per row, which is what you need to compare each row’s date to the slicer values.
Assumption- 'Service Request - Last Half Year' - 'Service Request -Dates' has One-Many relationship with Request Number.

Hope this helps!

[v-sdhruv]

Hi @Anonymous ,

Can you please try this?

isInRange =
IF(
    'table'[rowDate] >= [periodFromDate] &&
    'table'[rowDate] <= [periodToDate],
    1,
    0
)

If this still doesnt solve your issue, can you please share a sample file/ sample data  so that we can assist you better.

How to provide sample data 

Thank You.

[Anonymous]

First, thanks for your help.

The offered solution doesn't work at all. It can't work. 
Please note that "periodFromDate" and "periodToDate" are measures. 

While you define column as you offered, It doesn't work with the signle value.
You can't define measure with column... you must use function (such: min, max, selectedvalue...) - but this is my original issue

[Anonymous]

Thanks for you response. I have tried your solution - it doesn't work 

please find screen shots that demonstrate the issue:

This is the definition of date range and period length. At the right side - you may see the measures based on the date range and the period length.

this is the date range and period length information

this is the measure definition (I have tried with min/max/selectedvalue functions - all return the same value - 1

This is data sample - while, based on the Slicer and period length values - I want that only the "red marked" data will have the value 1. All the rows - should have the value 0.

The relationships between "servcie request - last half year" table and "service request - dates" is "one to one" and the next screen shots show the relationships' definitions:

while the second is "many to many" relationship, but with the first relationship definition - actually it is one-to-one.

I will be happy to hear from you about any idea for appropriate solution.
thanks!

[mdaatifraza5556]

Hi @Anonymous 

Could you please try the below DAX?

isInRange =
VAR fromDate = [periodFromDate]
VAR toDate = [periodToDate]
VAR rowDate = MAX('table'[rowDate]) -- or MIN, depending on your dataset
RETURN
IF(rowDate >= fromDate && rowDate < toDate, 1, 0)


If this does not then could you please provide some sample dataset.


If this answers your questions, kindly accept it as a solution and give kudos.

[Anonymous]

Thanks for you response. I have tried your solution - it doesn't work 

please find screen shots that demonstrate the issue:

This is the definition of date range and period length. At the right side - you may see the measures based on the date range and the period length.

this is the date range and period length information

this is the measure definition (I have tried with min/max/selectedvalue functions - all return the same value - 1

Measure 7 =

var fromDate = [periodFromDate]

var toDate = [periodEndDate]

var rowDate = SELECTEDVALUE('בקשות שירות - חצי שנה אחרונה'[REQUEST_CREATE_DATE])

return if (rowDate >= fromDate && rowDate < toDate , 1 , 0 )

This is data sample - while, based on the Slicer and period length values - I want that only the "red marked" data will have the value 1. All the rows - should have the value 0.

The relationships between "servcie request - last half year" table and "service request - dates" is "one to one" and the next screen shots show the relationships' definitions:

while the second is "many to many" relationship, but with the first relationship definition - actually it is one-to-one.

I will be happy to hear from you about any idea for appropriate solution.
thanks!