Three columns on one table, need to return the earliest

bradytb — Tue, 09 Jul 2024 23:45:41 GMT

I have one table that is being used (Pro Staff Info)

I have three separate columns that include a date or an N/A.

Pro Staff Info

Columns:

Initial Cert Exp (there is a date in this column if they have this type of cert, N/A if they don't)

Conditional Cert Exp (there is a date in this column if they have this type of cert, N/A if they don't)

Continuing Cert Exp( there is a date in this column if they have this type of cert, N/A if they don't)

I need a way to compare these columns and return the earliest date of the 3 columns.

The staff can have more than one type of cert to expire.

Ex: Sally has an initial cert expiring 6/30/2025 and a conditional cert expiring 6/30/2026. I would need a column that just returns the earliest date (in this example 6/30/2025).

The purpose is for the admin assistants to know when someone is expiring and starting the process to recertify.

Re: Three columns on one table, need to return the earliest

Anonymous — Wed, 10 Jul 2024 03:06:39 GMT

Hi bradytb

Based on your needs, I have created the following table.

Then you can use the following dax to get the result you want:

Measure = VAR select_name = SELECTEDVALUE('Pro Staff Info'[Name]) RETURN MINX(FILTER(ALL('Pro Staff Info'),'Pro Staff Info'[Name]=select_name && 'Pro Staff Info'[Date]<>BLANK()),'Pro Staff Info'[Date])

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Three columns on one table, need to return the earliest in DAX Commands and Tips

Three columns on one table, need to return the earliest

Re: Three columns on one table, need to return the earliest