var to calculate min with condition

[Anonymous]

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"))

[tamerj1]

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]

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.

[tamerj1]

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

[Anonymous]

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.

[tamerj1]

@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]

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

[tamerj1]

@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]

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

[tamerj1 · Accepted solution]

@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".

Please try

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]

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.

thanks a lot for your help and quick reply.

Regards,

[tamerj1]

@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]

Ok thanks a lot for all your help

[tamerj1]

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

[Anonymous]

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]

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])

[amitchandak]

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

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