Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Solved! Go to 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 )
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"
)
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?
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 )
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] )
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 )
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.
Ok thanks a lot for all your help
@Anonymous
Please consider marking the correct solution as acceptable. Thank you.
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 , if they are multiple tables append then and then try to have measure like
minx(filter(Table, [value] <> "N"), Table[Date])
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |