Visualize in a view, column value for Nth value in rank

[GustavoKamchen]

Hello.

I have a list of factory faults caused by production line, and i need to create 3 views (tables) showing which was the fault components and quantities for the 1st, 2nd and 3rd fault reasons, like below:

Visual 1 - Table (Done):
Top 3 fault reasons (last week - date filtered in Visual):

RANK	FAULT REASON	FAULT QUANTITY
1	FAULT 37	5
2	FAULT 28	3
3	FAULT 10	3

View 2 - Table (Pendind):
Components for 1st FAULT REASON (FAULT 37) in the last week (date filtered in visual):

FAULT COMPONENT	FAULT QUANTITY
COMPONENT 14	4
COMPONENT 7	1

View 3 - Table (Pendind):
Components for 2nd FAULT REASON (FAULT 28) in the last week (date filtered in visual):

FAULT COMPONENT	FAULT QUANTITY
COMPONENT 8	2
COMPONENT 15	1

View 4 - Table (Pendind):
Components for 3rd FAULT REASON (FAULT 10) in the last week (date filtered in visual):

FAULT COMPONENT	FAULT QUANTITY
COMPONENT 1	1
COMPONENT 14	1
COMPONENT 22	1

Note that every week the top 3 FAULT REASONS can change, so I can't filter FAULT REASON directly in the table visual.

How can I create the individual table visuals to show the components for each fault reason? In all visuals, date is filtered in the visual to get only faults that happened in the last calendar week.

Sample data:

DATE	NOTE NUMBER	FAULT REASON	COMPONENT	FAULT QUANTITY
6/19/2023	100000053	FAULT 37	COMPONENT 7	1
6/19/2023	100000054	FAULT 28	COMPONENT 15	1
6/19/2023	100000055	FAULT 28	COMPONENT 8	1
6/19/2023	100000056	FAULT 27	COMPONENT 22	1
6/19/2023	100000057	FAULT 37	COMPONENT 14	1
6/19/2023	100000058	FAULT 29	COMPONENT 9	1
6/19/2023	100000059	FAULT 37	COMPONENT 14	1
6/20/2023	100000060	FAULT 15	COMPONENT 22	1
6/20/2023	100000061	FAULT 10	COMPONENT 1	1
6/20/2023	100000062	FAULT 21	COMPONENT 4	1
6/20/2023	100000063	FAULT 16	COMPONENT 4	1
6/20/2023	100000064	FAULT 8	COMPONENT 8	1
6/20/2023	100000065	FAULT 28	COMPONENT 8	1
6/20/2023	100000066	FAULT 13	COMPONENT 8	1
6/20/2023	100000067	FAULT 10	COMPONENT 22	1
6/20/2023	100000068	FAULT 37	COMPONENT 14	1
6/20/2023	100000069	FAULT 30	COMPONENT 12	1
6/20/2023	100000070	FAULT 37	COMPONENT 14	1
6/20/2023	100000071	FAULT 15	COMPONENT 22	1
6/20/2023	100000072	FAULT 22	COMPONENT 4	1
6/20/2023	100000073	FAULT 32	COMPONENT 6	1
6/20/2023	100000074	FAULT 4	COMPONENT 3	1
6/21/2023	100000075	FAULT 2	COMPONENT 5	1
6/21/2023	100000076	FAULT 12	COMPONENT 7	1
6/21/2023	100000077	FAULT 36	COMPONENT 19	1
6/21/2023	100000078	FAULT 38	COMPONENT 10	1
6/21/2023	100000079	FAULT 24	COMPONENT 2	1
6/21/2023	100000080	FAULT 12	COMPONENT 10	1
6/22/2023	100000081	FAULT 35	COMPONENT 27	1
6/22/2023	100000082	FAULT 32	COMPONENT 26	1
6/22/2023	100000083	FAULT 14	COMPONENT 14	1
6/22/2023	100000084	FAULT 27	COMPONENT 22	1
6/22/2023	100000085	FAULT 1	COMPONENT 10	1
6/22/2023	100000086	FAULT 26	COMPONENT 20	1
6/22/2023	100000087	FAULT 9	COMPONENT 19	1
6/22/2023	100000088	FAULT 26	COMPONENT 11	1
6/22/2023	100000089	FAULT 20	COMPONENT 27	1
6/22/2023	100000090	FAULT 29	COMPONENT 9	1
6/23/2023	100000091	FAULT 5	COMPONENT 27	1
6/23/2023	100000092	FAULT 26	COMPONENT 20	1
6/23/2023	100000093	FAULT 17	COMPONENT 19	1
6/23/2023	100000094	FAULT 9	COMPONENT 19	1
6/23/2023	100000095	FAULT 5	COMPONENT 27	1
6/23/2023	100000096	FAULT 12	COMPONENT 24	1
6/23/2023	100000097	FAULT 10	COMPONENT 14	1

[v-yangliu-msft · Accepted solution]

Hi  @GustavoKamchen ,

Here are the steps you can follow：

1. Create calculated column.

Sum_Fault =
COUNTX(
    FILTER(ALL('Table'),
    'Table'[FAULT REASON]=EARLIER('Table'[FAULT REASON])),[FAULT QUANTITY])

Rank =
RANKX(
    ALL('Table'),'Table'[Sum_Fault],,DESC,Dense)

Put it in Visual and set "is less than or equal to" = 3 in Filter

2. Create measure.

Flag_rank1 =
var _rank1=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Rank]=1),"1",[FAULT REASON])
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[FAULT REASON] in _rank1),"1",[COMPONENT])
return
IF(
    MAX('Table'[COMPONENT]) in _column,1,0)

Flag_rank2 =
var _rank2=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Rank]=2),"2",[FAULT REASON])
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[FAULT REASON] in _rank2),"2",[COMPONENT])
return
IF(
    MAX('Table'[COMPONENT]) in _column,1,0)

Flag_rank3 =
var _rank1=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Rank]=3),"3",[FAULT REASON])
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[FAULT REASON] in _rank1),"3",[COMPONENT])
return
IF(
    MAX('Table'[COMPONENT]) in _column,1,0)

3. Place [Flag_rank1], [Flag_rank2], [Flag_rank3] in the corresponding Filter to set is=1.

4. Result:

Best Regards,

Liu Yang

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