topic RankX Weird Behavior With Filter in Power Query

RankX Weird Behavior With Filter

Robert4049 — Thu, 19 Aug 2021 21:55:57 GMT

Okay talked about confused, I went ahead and removed blank arrive date times but the still show up when I filter and I can't figure this out. Any hints?

MedicalUnitFirstDue = VAR CurrentExposureID = Apparatus[Run Number] VAR EngineTypeID = {"11", "12", "76"} RETURN IF ( Apparatus[Apparatus Type ID] IN EngineTypeID, RANKX ( FILTER ( Apparatus, Apparatus[Run Number] = CurrentExposureID && ISBLANK(Apparatus[Arrival Date/Time]) == FALSE() && Apparatus[Apparatus Type ID] IN EngineTypeID ), Apparatus[Arrival Date/Time], , DESC, DENSE ), BLANK () )

Re: RankX Weird Behavior With Filter

sevenhills — Fri, 20 Aug 2021 05:52:09 GMT

&& Apparatus[Arrival Date/Time] <> BLANK ()

Try this ...

Re: RankX Weird Behavior With Filter

v-kelly-msft — Mon, 23 Aug 2021 08:02:50 GMT

Hi Robert4049 ,

Could you pls share your .pbix file for test?

Remember to remove the confidentail information.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

Re: RankX Weird Behavior With Filter

Robert4049 — Mon, 23 Aug 2021 16:07:59 GMT

Hey Kelly,

I rewrote the column as

IF ( Apparatus[Arrival Date/Time] <> BLANK() && Apparatus[Apparatus Type ID] IN EngineTypeID, IF ( RANKX ( FILTER ( Apparatus, Apparatus[Run Number] == CurrentExposureID && Apparatus[Apparatus Type ID] IN EngineTypeID ), Apparatus[Arrival Date/Time], Apparatus[Arrival Date/Time], ASC, Skip ) = 1, TRUE (), FALSE () ), FALSE () )

Unfortunately there's no good way to redact the confidential data.

Re: RankX Weird Behavior With Filter

sevenhills — Mon, 23 Aug 2021 19:39:08 GMT

I will ask you to try one more attempt, (as it is tough without sample data and expected output data, for someone to provide DAX)

Let us see this is what you need as first due date time for every run number. Once we get this one resolved, then you can build the rank similarly.

Below DAX, gives the first Arrival Date time for the Run number.

I used your filter of type id, and non blank arrival date time rows

MedicalUnitFirstDue = VAR CurrentExposureID = Apparatus[Run Number] VAR EngineTypeID = {"11", "12", "76"} Var _c = CALCULATE( Min(Apparatus[Arrival Date/Time]), filter( allexcept(Apparatus, Apparatus[Run Number], Apparatus[Apparatus Type ID], Apparatus[Arrival Date/Time]), Apparatus[Run Number] = CurrentExposureID && Apparatus[Arrival Date/Time] <> BLANK() && Apparatus[Apparatus Type ID] IN EngineTypeID ) ) RETURN # _c IF (NOT IsBlank(_c), _c, BLANK ())

... not the full solution with Rankx, but trying to see the min date is working right.

Re: RankX Weird Behavior With Filter

v-kelly-msft — Wed, 25 Aug 2021 01:23:29 GMT

Hi sevenhills ,

Without actual data,it would be a little tough to find a solution.

Try below expression:

measure = IF ( RANKX ( FILTER ( Apparatus, Apparatus[Run Number] == CurrentExposureID && Apparatus[Apparatus Type ID] IN EngineTypeID && pparatus[Arrival Date/Time] <> BLANK () && Apparatus[Apparatus Type ID] IN EngineTypeID ), Apparatus[Arrival Date/Time], Apparatus[Arrival Date/Time], ASC, SKIP ) = 1, TRUE (), FALSE () )

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!