Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

 

Screenshot 6.jpg

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

View solution in original post

16 REPLIES 16
tamerj1
Super User
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

aahmad28_0-1657540991649.png

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.

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.

@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

aahmad28_0-1657542167501.png

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

@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

aahmad28_0-1657543135082.png

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

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

thanks a lot for your help and quick reply.

 

Regards,

@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

@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

aahmad28_1-1657542760037.png

 

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])
amitchandak
Super User
Super User

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

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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