cancel
Showing results for
Did you mean:

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.

Anonymous
Not applicable

## var to calculate min with condition

I have few columns that have dates and with each date there are columns associated that has their status. I need to find the min of the dates where status is not equal to "N". I used the following formula but its giving me minimum of dates but not ignore the ones whos status is equal to N. Can someone help me to correct the formula, please?

Low-band Date = Var temp = {'Pace Tracker'[PJ(5900) L600 OnAir],'Pace Tracker'[PJ(6000) L700 OnAir],'Pace Tracker'[PJ(5950) N600 OnAir]}
var temp1 = {'Pace Tracker'[PJ(5900) L600 OnAir Sts],'Pace Tracker'[PJ(6000) L700 OnAir Sts],'Pace Tracker'[PJ(5950) N600 OnAir Sts]}
Return
calculate (minx(temp,[value]), filter(temp1,[value] <> "N"))

1 ACCEPTED SOLUTION
Super User

@Anonymous
You are right. I misunderstood your requirement. You are trying to create a calculated column of the minimum date along the same row context given that the status of that date is not equal to "N".

``````Low-band Date =
VAR MinPJ5900 =
COALESCE (
IF (
'Pace Tracker'[PJ(5900) L600 OnAir Sts] <> "N",
'Pace Tracker'[PJ(5900) L600 OnAir]
),
DATE ( 3000, 1, 1 )
)
VAR MinPJ5950 =
COALESCE (
IF (
'Pace Tracker'[PJ(5950) N600 OnAir Sts] <> "N",
'Pace Tracker'[PJ(5950) N600 OnAir]
),
DATE ( 3000, 1, 1 )
)
VAR MinPJ6000 =
COALESCE (
IF (
'Pace Tracker'[PJ(6000) L700 OnAir Sts] <> "N",
'Pace Tracker'[PJ(6000) L700 OnAir]
),
DATE ( 3000, 1, 1 )
)
RETURN
MIN ( MIN ( MinPJ5900, MinPJ5950 ), MinPJ6000 )``````
16 REPLIES 16
Super User

Hi @Anonymous
You may try

``````Low-band Date =
CALCULATE (
MIN (
MIN (
MIN ( 'Pace Tracker'[PJ(5900) L600 OnAir] ),
MIN ( 'Pace Tracker'[PJ(6000) L700 OnAir] )
),
MIN ( 'Pace Tracker'[PJ(5950) N600 OnAir] )
),
'Pace Tracker'[PJ(5900) L600 OnAir Sts] <> "N",
'Pace Tracker'[PJ(6000) L700 OnAir Sts] <> "N",
'Pace Tracker'[PJ(5950) N600 OnAir Sts] <> "N"
)``````
Anonymous
Not applicable

it says circular dependency. Also I may have more columns for few cases as well. the formula is working fine to calculate the min but when I am putting condition not to use status "N" for all then the dates doesn't work.

Super User

Hi @Anonymous
Are you creating a mesure of a calculated column?

Anonymous
Not applicable

I have multiple columns of dates and their status  for each date column

I am trying to pull min for dates for each row where status for the dates is not equal to "N". and that is not a measure, its will be a new column.

Super User

@Anonymous
Yes but applying the filter in this way won't work. If you have too many columns then it would be wise to unpivot them using power query then the DAX would be much more simple. Otherwise please try the following nested MIN. YOu can add one more VAR and one more MIN in case you need to include one more column

``````Low-band Date =
VAR MinPJ5900 =
CALCULATE (
MIN ( 'Pace Tracker'[PJ(5900) L600 OnAir] ),
'Pace Tracker'[PJ(5900) L600 OnAir Sts] <> "N"
)
VAR MinPJ5950 =
CALCULATE (
MIN ( 'Pace Tracker'[PJ(5950) N600 OnAir] ),
'Pace Tracker'[PJ(5950) N600 OnAir Sts] <> "N"
)
VAR MinPJ6000 =
CALCULATE (
MIN ( 'Pace Tracker'[PJ(6000) L700 OnAir] ),
'Pace Tracker'[PJ(6000) L700 OnAir Sts] <> "N"
)
RETURN
MIN ( MIN ( MinPJ5900, MinPJ5950 ), MinPJ6000 )``````
Anonymous
Not applicable

its giving me same error again and for min I am trying to find that for the row not the min of the column, I think the min that you are calculating in this is for the column. I am trying to find the minimum on the dates for that row where that row date status is <> N for that date

Super User

@Anonymous
This is what the code is trying to calculate but seems this is creating a circular dependancy. Let's try another approach

``````Low-band Date =
VAR T1 =
UNION (
SELECTCOLUMNS (
'Pace Tracker',
"Value", 'Pace Tracker'[PJ(5900) L600 OnAir],
"Status", 'Pace Tracker'[PJ(5900) L600 OnAir Sts]
),
SELECTCOLUMNS (
'Pace Tracker',
"Value", 'Pace Tracker'[PJ(5950) N600 OnAir],
"Status", 'Pace Tracker'[PJ(5950) N600 OnAir Sts]
),
SELECTCOLUMNS (
'Pace Tracker',
"Value", 'Pace Tracker'[PJ(6000) L700 OnAir],
"Status", 'Pace Tracker'[PJ(6000) L700 OnAir Sts]
)
)
VAR T2 =
FILTER ( T1, [Status] <> "N" )
RETURN
MINX ( T2, [Value] )``````
Anonymous
Not applicable

its giving me value for the min of all rows, not per row level

Super User

@Anonymous
You are right. I misunderstood your requirement. You are trying to create a calculated column of the minimum date along the same row context given that the status of that date is not equal to "N".

``````Low-band Date =
VAR MinPJ5900 =
COALESCE (
IF (
'Pace Tracker'[PJ(5900) L600 OnAir Sts] <> "N",
'Pace Tracker'[PJ(5900) L600 OnAir]
),
DATE ( 3000, 1, 1 )
)
VAR MinPJ5950 =
COALESCE (
IF (
'Pace Tracker'[PJ(5950) N600 OnAir Sts] <> "N",
'Pace Tracker'[PJ(5950) N600 OnAir]
),
DATE ( 3000, 1, 1 )
)
VAR MinPJ6000 =
COALESCE (
IF (
'Pace Tracker'[PJ(6000) L700 OnAir Sts] <> "N",
'Pace Tracker'[PJ(6000) L700 OnAir]
),
DATE ( 3000, 1, 1 )
)
RETURN
MIN ( MIN ( MinPJ5900, MinPJ5950 ), MinPJ6000 )``````
Anonymous
Not applicable

that did worked now. thanks a lot. Can you explain what does coalesce and date (3000,1,1) will do? I just want to make sure when I am doing that to more columns it still work.

Regards,

Super User

@Anonymous
This is just to return a date in the future incase the if condition is not stisfied. As otherwise IF will return Blnak and then MIN will return Blank as minimum value.

Anonymous
Not applicable

Ok thanks a lot for all your help

Super User

@Anonymous
Please consider marking the correct solution as acceptable. Thank you.

Anonymous
Not applicable

here is some sample data and in red this is what I wan to calculate. Ignore the date where there is "N' for its stauts and calculate the minimum for the remaining on the specific row

Anonymous
Not applicable
its giving me error that can't find table temp, the one i created to get onair dates from different columns

Low-band Date = Var temp = {'Pace Tracker'[PJ(5900) L600 OnAir],'Pace Tracker'[PJ(6000) L700 OnAir],'Pace Tracker'[PJ(5950) N600 OnAir]}
var temp1 = {'Pace Tracker'[PJ(5900) L600 OnAir Sts],'Pace Tracker'[PJ(6000) L700 OnAir Sts],'Pace Tracker'[PJ(5950) N600 OnAir Sts]}
Return
minx(FILTER(temp1, [Value] <> "N"),temp[value])
Super User

@Anonymous , if they are multiple tables append then and then try to have measure like

minx(filter(Table, [value] <> "N"), Table[Date])

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors