want to see within GOALIMIT ROW

[ABC11]

Hello to All,

Only want to see one row per LEMNUM - within GOALIMIT when LEMNUM is same- color in BLUE

As example

LEMNUM 322185 - LINE_COST 279,000>250,000 and 279,000<=500,000 so PERSONID- MATTHEGE do not have enough GOALIMIT so approver would be GLENNS so I would like to show only blue row 

LEMNUM 322197- MATTHEGE has enough GOALIMIT to approve.

PERSONGROUP	NAME	CNRCMS	LEMNUM	WAPPR_DATE	PERSONID	GOALIMIT	LINE_COST	NO_OF_DAYS
LEMHZHM	HEAVY	R015589	322185	1/8/2024	GLENNS	500,000.00	279,000.00	0
LEMHZHM	HEAVY	R015589	322185	1/8/2024	MATTHEGE	250,000.00	279,000.00	0
LEMHZHM	HEAVY	R015589	322197	1/8/2024	GLENNS	500,000.00	373,200.00	0
LEMHZHM	HEAVY	R015589	322197	1/8/2024	MATTHEGE	250,000.00	233,000.00	0
LMBP02	GRAHAM	R033112	320427	1/1/2024	DANSO	250,000.00	119,612.45	7
LMBP02	GRAHAM	R033112	320427	1/1/2024	ROBBERTR	500,000.00	119,612.45	7
LMBP08	ABC	R018007	322029	1/8/2024	ANDREWC	100,000.00	259,950.40	0
LMBP08	ABC	R018007	322029	1/8/2024	BLAKETA	250,000.00	259,950.40	0
LMBP08	ABC	R018007	322029	1/8/2024	CHRISFAU	250,000.00	259,950.40	0
LMBP08	ABC	R018007	322029	1/8/2024	DANSO	250,000.00	259,950.40	0
LMBP08	ABC	R018007	322029	1/8/2024	DANTEC	50,000.00	259,950.40	0
LMBP08	ABC	R018007	322029	1/8/2024	GREGMA	250,000.00	259,950.40	0
LMBP08	ABC	R018007	322029	1/8/2024	JEFFWH	2,500,000.00	259,950.40	0
LMBP08	ABC	R018007	322029	1/8/2024	JOHNAL	2,000,000.00	259,950.40	0
LMBP08	ABC	R018007	322029	1/8/2024	ROBBERTR	500,000.00	259,950.40	0
LMBP09	XYZ	R032712	315703	1/2/2024	DANSO	250,000.00	16,756.40	6
LMBP09	XYZ	R032712	315703	1/2/2024	JASONTA	2,500,000.00	16,756.40	6
LMBP09	XYZ	R032712	315703	1/2/2024	ROBBERTR	500,000.00	16,756.40	6
LMBP09	XYZ	R032712	315704	1/2/2024	DANSO	250,000.00	17,686.50	6
LMBP09	XYZ	R032712	315704	1/2/2024	JASONTA	2,500,000.00	17,686.50	6
LMBP09	XYZ	R032712	315704	1/2/2024	ROBBERTR	500,000.00	17,686.50	6
LMBP09	XYZ	R032712	315884	1/8/2024	DANSO	250,000.00	11,257.84	0
LMBP09	XYZ	R032712	315884	1/8/2024	JASONTA	2,500,000.00	11,257.84	0
LMBP14	DGF	R019151	319696	1/8/2024	CHADBE	2,500,000.00	3,218.87	0
LMBP14	DGF	R019151	319696	1/8/2024	CHRISFAU	250,000.00	3,218.87	0
LMBP14	DGF	R019151	319696	1/8/2024	DANTEC	50,000.00	3,218.87	0
LMBP14	DGF	R019151	319696	1/8/2024	NATM	250,000.00	3,218.87	0

Thanks for your time and help

ABC11

[Fowmy · Accepted solution]

@ABC11 
There are multiples ways. You can create a new table that filters only the final approvers.
under modling tab, create a new table as follows:

New Table = FILTER( Table03 , Table03[PERSONID] = Table03[APPROVER] )

Result:

[ABC11 · Accepted solution]

Thanks lots for your time

[Fowmy]

@ABC11 

Please explain
1. Why the LINE COST numbers are different for 322197 ?
2. For number 322029 ROBBERTR qualifies first to approve but you highlighted JEFFWH

[ABC11]

Hello Fowmy,

LEMNUM#322197-it was my mistake(when I was dummy preparing data) it should be 233,000

LEMNUM#322029-you are correct ROBBERTR should be the approver.

Sorry for this mistake

Thanks for looking into-would you please help me here

[Fowmy]

@ABC11 

Add the following calculated column to your table:

APPROVER = 
VAR __LemnumFiltered =  FILTER( Table03 , Table03[LEMNUM] = EARLIER(Table03[LEMNUM]) && Table03[ GOALIMIT ] > Table03[ LINE_COST ] )
VAR __ApproverAmount = MINX( __LemnumFiltered , Table03[ GOALIMIT ] )
VAR __Result = MAXX(  FILTER( __LemnumFiltered , Table03[ GOALIMIT ] = __ApproverAmount ) , Table03[PERSONID] )
RETURN
    __Result

[ABC11]

Thanks Lots Fowmy,

How can I remove duplicate row. 

Is it possible to have one line for one LEMNUM

Please

[Fowmy · Accepted solution]

@ABC11 
There are multiples ways. You can create a new table that filters only the final approvers.
under modling tab, create a new table as follows:

New Table = FILTER( Table03 , Table03[PERSONID] = Table03[APPROVER] )

Result:

[ABC11]

Hello Towmy,

I need more help here,

In below example more than one aprpover has same GOALIMIT. How can I poplulate all name with same GOALIMIT  (within one row or individual row)

Thanks again

[Fowmy]

@ABC11 

How do you want to see the results if more than one approver exists? All of them ? this will have  multiple rows per doc number. If you want to see a single line, I will have to combone the approver column values with concatnation like: AISAW|DANSO|SHANEC... and so on. I am not sure how you will make use this data.

Please confirm.

[ABC11]

Hello Fowmy,

Single line will work for me.

Idea is to send email to aprpover.

Thanks lots

[Fowmy]

@ABC11 

To be clear, when you have a single line with approvers names concatenated together, I'am not sure how it will help you with sending mails. please check and confirm, better if you could share the expected output with your sample data.

[ABC11]

Hello Fowmy,

Thanks for your time

Sample Data

PERSONGROUP	VENDOR_NAME	CNRCMSNUM	LEMNUM	WAPPR_DATE	CNRGOALIMIT	LINE_COST	NO_OF_DAYS	PERSONID
LMU216	ABD	R024292	321773	1/8/2024	250,000.00	23,543.52	3	AISAW
LMU216	ABD	R024293	321773	1/8/2024	250,000.00	23,543.52	3	BLAKETA
LMU216	ABD	R024294	321773	1/8/2024	250,000.00	23,543.52	3	CHRISCOM
LMU216	ABD	R024295	321773	1/8/2024	25,000.00	23,543.52	3	CRAIGHOO
LMU216	ABD	R024296	321773	1/8/2024	250,000.00	23,543.52	3	DANSO
LMU216	ABD	R024297	321773	1/8/2024	2,500,000.00	23,543.52	3	JASONTA
LMU216	ABD	R024298	321773	1/8/2024	250,000.00	23,543.52	3	JONO
LMU216	ABD	R024299	321773	1/8/2024	250,000.00	23,543.52	3	STEPHETE
LMU216	ABD	R024300	321773	1/8/2024	100,000.00	23,543.52	3	WESLEYWE
LMBP08	GEF	R018007	322447	1/10/2024	100,000.00	150,150.00	1	ANDREWC
LMBP08	GEF	R018008	322447	1/10/2024	250,000.00	150,150.00	1	BLAKETA
LMBP08	GEF	R018009	322447	1/10/2024	250,000.00	150,150.00	1	CHRISFAU
LMBP08	GEF	R018010	322447	1/10/2024	250,000.00	150,150.00	1	DANSO
LMBP08	GEF	R018011	322447	1/10/2024	50,000.00	150,150.00	1	DANTEC
LMBP08	GEF	R018012	322447	1/10/2024	250,000.00	150,150.00	1	GREGMA
LMES	LET	R024683	322925	1/10/2024	10,000.00	200,000.00	1	HEATHECO
LMES	LET	R024684	322925	1/10/2024	250,000.00	200,000.00	1	JOHP
LMES	LET	R024685	322925	1/10/2024	250,000.00	200,000.00	1	ROBERRIT

OUTPUT

PERSONGROUP	VENDOR_NAME	CNRCMSNUM	LEMNUM	WAPPR_DATE	CNRGOALIMIT	LINE_COST	NO_OF_DAYS	PERSONID
LMU216	ABD	R024295	321773	1/8/2024	25,000.00	23,543.52	3	CRAIGHOO
LMBP08	GEF	R018008	322447	1/10/2024	250,000.00	150,150.00	1	BLAKETA;CHRISFAU;|DANSO;|GREGMA
LMES	LET	R024684	322925	1/10/2024	250,000.00	200,000.00	1	JOHP;ROBERRIT

Thanks lot

[ABC11]

Hello Fowny,

Can I request to you to help me here.

Please

Thanks

[ABC11]

Hello Fowny,

I would like to request see my request.

Please

[Fowmy · Accepted solution]

@ABC11 

Here are the modified codes:

Calculated Column:

APPROVER = 
VAR __LemnumFiltered =  FILTER( Table03 , Table03[LEMNUM] = EARLIER(Table03[LEMNUM]) && Table03[ GOALIMIT ] > Table03[ LINE_COST ] )
VAR __ApproverAmount = MINX( __LemnumFiltered , Table03[ GOALIMIT ] )
VAR __Result = CONCATENATEX(  FILTER( __LemnumFiltered , Table03[ GOALIMIT ] = __ApproverAmount ) , Table03[PERSONID],"|" )
RETURN
    __Result

New Table:

New Table = FILTER( Table03 ,  CONTAINSSTRING( Table03[APPROVER], Table03[PERSONID] ) )

[ABC11]

Thanks lots

[ABC11 · Accepted solution]

Thanks lots for your time

[Greg_Deckler]

@ABC11 Not entirely sure I understand why one row is preferred over another but in general you need a Complex Selector. The Complex Selector - Microsoft Fabric Community

[ABC11]

Hello GREG,

Thanks for your quick respond.

LEMNUM 322185- MATTHEGE can't approve this becuase his GOALIMIT upto 250,000 and my LINE_COST value is 279,000. so in this case GLENNS is approver.

LEMMUN 322197- MATTHEGE has enough GOALIMIT to approve. so do not want to forward this to GLENNS(GLENNS has higer GOALIMIT compare to MATTHEGE)

I hope - this will help us

Thanks again