Solved: Restricted number of most recent Items between two...

Daveyt69 · ‎06-03-2019

Hello I have what I hope is an interesting problem (well a lot problems to be precise) and I am hoping someone can help me. I am currently new to Power BI.

I did manage to get this working in Crystal Reports using SQL but this option does not appear to work in Power Bi. What I need is to obtain a list of staff who have done a certain number of tests, say 10 tests, in a given time period. To further cloud, the issue it must be the most recent 10 tests and I also need a list of each unique test references.

What I really need is shown in the Required Output table below.

The table below called Master Data shows a snapshot of the data available if this helps.

I did think about using the Top N filter but this doesn’t give me what I need. A running total of the number of tests could work I think but again I’m not sure how to do this for each member of staff.

Any help you can give me, it would be appreciated.

Master Data

STAFF_NO TEST_REF DATE_OF_TEST SLOT_TIME

377444 01 01/08/2018 08:40:00

377444 02 01/08/2018 10:40:00

377444 03 02/08/2018 11:40:00

544555 01 05/08/2018 10:40:00

599455 01 06/08/2018 08:40:00

405405 01 01/08/2018 10:40:00

405405 02 02/08/2018 10:40:00

405405 03 05/08/2018 10:40:00

405405 04 06/08/2018 08:40:00

405405 05 06/08/2018 09:40:00

405405 06 06/08/2018 10:40:00

405405 07 06/08/2018 11:40:00

405405 08 07/08/2018 08:40:00

405405 09 07/08/2018 09:40:00

405405 10 07/08/2018 10:40:00

405405 11 08/08/2018 08:40:00

405405 12 08/08/2018 09:40:00

366255 01 06/08/2018 09:40:00

366255 02 06/08/2018 09:40:00

366255 03 06/08/2018 09:40:00

366255 04 06/08/2018 09:40:00

366255 05 06/08/2018 09:40:00

366255 06 06/08/2018 09:40:00

366255 07 06/08/2018 09:40:00

366255 08 06/08/2018 09:40:00

366255 09 06/08/2018 09:40:00

366255 10 06/08/2018 09:40:00

366255 11 06/08/2018 09:40:00

366255 12 06/08/2018 09:40:00

366255 13 06/08/2018 09:40:00

366255 14 06/08/2018 09:40:00

366255 15 06/08/2018 09:40:00

366255 16 06/08/2018 09:40:00

366255 17 06/08/2018 09:40:00

366255 18 06/08/2018 10:40:00

366255 19 06/08/2018 11:40:00

366255 20 06/08/2018 12:40:00

366255 21 09/08/2018 08:40:00

366255 22 09/08/2018 09:40:00

366255 23 09/08/2018 10:40:00

366255 24 09/08/2018 12:40:00

366255 25 09/08/2018 14:40:00

366255 26 09/08/2018 15:40:00

Overall Required Output

STAFF_NO TEST_REF DATE_OF_TEST SLOT_TIME

405405 03 05/08/2018 10:40:00

405405 04 06/08/2018 08:40:00

405405 05 06/08/2018 09:40:00

405405 06 06/08/2018 10:40:00

405405 07 06/08/2018 11:40:00

405405 08 07/08/2018 08:40:00

405405 09 07/08/2018 09:40:00

405405 10 07/08/2018 10:40:00

405405 11 08/08/2018 08:40:00

405405 12 08/08/2018 09:40:00

366255 17 06/08/2018 09:40:00

366255 18 06/08/2018 10:40:00

366255 19 06/08/2018 11:40:00

366255 20 06/08/2018 12:40:00

366255 21 09/08/2018 08:40:00

366255 22 09/08/2018 09:40:00

366255 23 09/08/2018 10:40:00

366255 24 09/08/2018 12:40:00

366255 25 09/08/2018 14:40:00

366255 26 09/08/2018 15:40:00

Thanks again for any help.

AlB · ‎06-04-2019

@Daveyt69

You explained it fine. I hadn't quite read the requirements properly. Try this instead. Edited: Change the ";" for "," if required, depending on your locale:

NewTable = 
VAR MinNumTestsRequired_ = 10 //Change here for the required number of tests
VAR LastN_ = 10 //Change here for the num of last tests to be shown   
RETURN 
FILTER (
    Table1;
    VAR LatestRef_ =
        CALCULATE ( MAX ( Table1[TEST_REF] ); ALLEXCEPT ( Table1; Table1[STAFF_NO] ) )
    VAR RefCount_ = 
        CALCULATE ( COUNT ( Table1[TEST_REF] ); ALLEXCEPT ( Table1; Table1[STAFF_NO] ) )
    RETURN
        Table1[TEST_REF] IN GENERATESERIES ( LatestRef_ - LastN_ + 1; LatestRef_ ) && RefCount_ >= MinNumTestsRequired_
)

View solution in original post

AlB · ‎06-03-2019

Hi @Daveyt69

Assuming the TEST_REF column indicates, as it seems, the order in which the tests were taken, create a calculated table:

NewTable =
FILTER (
    Table1,
    VAR LatestRef_ =
        CALCULATE ( MAX ( Table1[TEST_REF] ), ALLEXCEPT ( Table1, Table1[STAFF_NO] ) )
    RETURN
        Table1[TEST_REF]
            IN GENERATESERIES ( LatestRef_ - 9, LatestRef_ )
)

or equivalently

NewTableV2 =
FILTER (
    Table1,
    VAR LatestRef_ =
        CALCULATE ( MAX ( Table1[TEST_REF] ), ALLEXCEPT ( Table1, Table1[STAFF_NO] ) )
    RETURN
        Table1[TEST_REF] >= LatestRef_ - 9 && Table1[TEST_REF] <= LatestRef_ )
)

If the TEST_REF column is text, create a new column converting it to an integer, and use that column in the code above instead. This could be done with TOPN as well, but believe it's simpler, and faster, this way.

PS: Actually, I believe you could do the conversion to integer within the filtering operation, without the need for an additional calculated colum.

Daveyt69 · ‎06-04-2019

Hi AlB,

Thanks for getting back to me so quickly.

I'm not sure if I explained what I need correctly as I've tried your both versions of your code and get the following output:

DATE_OF_TEST	SLOT_TIME	STAFF_NO	TEST_REF
01/08/2018 00:00	1899-12-30 08:40:00	377444	1
01/08/2018 00:00	1899-12-30 10:40:00	377444	2
02/08/2018 00:00	1899-12-30 11:40:00	377444	3
05/08/2018 00:00	1899-12-30 10:40:00	949960	4
06/08/2018 00:00	1899-12-30 08:40:00	1004860	5
06/08/2018 00:00	1899-12-30 09:40:00	771660	22
06/08/2018 00:00	1899-12-30 10:40:00	771660	24
06/08/2018 00:00	1899-12-30 11:40:00	771660	26
06/08/2018 00:00	1899-12-30 12:40:00	366255	20
07/08/2018 00:00	1899-12-30 08:40:00	405405	8
07/08/2018 00:00	1899-12-30 09:40:00	405405	9
07/08/2018 00:00	1899-12-30 10:40:00	405405	10
08/08/2018 00:00	1899-12-30 08:40:00	405405	11
08/08/2018 00:00	1899-12-30 09:40:00	405405	12
09/08/2018 00:00	1899-12-30 08:40:00	366255	21
09/08/2018 00:00	1899-12-30 09:40:00	366255	22
09/08/2018 00:00	1899-12-30 10:40:00	366255	23
09/08/2018 00:00	1899-12-30 12:40:00	366255	24
09/08/2018 00:00	1899-12-30 14:40:00	366255	25
09/08/2018 00:00	1899-12-30 15:40:00	366255	26

Which unfortunately is not what I need.

The selection should only show those staff who have conducted 10 tests and their last 10 tests and should disregard those who have not met this quota. Is this possible ?

If I'm not explaining myself correctly or you need any info please let me know.

Regards

@Daveyt69

AlB · ‎06-04-2019

@Daveyt69

You explained it fine. I hadn't quite read the requirements properly. Try this instead. Edited: Change the ";" for "," if required, depending on your locale:

NewTable = 
VAR MinNumTestsRequired_ = 10 //Change here for the required number of tests
VAR LastN_ = 10 //Change here for the num of last tests to be shown   
RETURN 
FILTER (
    Table1;
    VAR LatestRef_ =
        CALCULATE ( MAX ( Table1[TEST_REF] ); ALLEXCEPT ( Table1; Table1[STAFF_NO] ) )
    VAR RefCount_ = 
        CALCULATE ( COUNT ( Table1[TEST_REF] ); ALLEXCEPT ( Table1; Table1[STAFF_NO] ) )
    RETURN
        Table1[TEST_REF] IN GENERATESERIES ( LatestRef_ - LastN_ + 1; LatestRef_ ) && RefCount_ >= MinNumTestsRequired_
)

Daveyt69 · ‎06-04-2019

Hello @AlB

I actually can follow that.....I think

However when I try to use it I get the following sysntax error after the initial FILTER - The syntax for ';' is incorrect.

Any ideas as to why ?

Thanks again for all your help.

Daveyt69

v-shex-msft · ‎06-19-2019

HI @Daveyt69 ,

You can use ',' to replace ';', it may related to your device settings.(some of devices use ';' as expression separator, the other devices use ',')

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Restricted number of most recent Items between two dates

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly