MAX and Index multiple columns from one table to another table

[Saxon10]

Hi,

I have a two tables are data and report.

In data table the following columns are Size A, Size B, Size C and Rank.

In Data table, I created rank column based on the sizes (Size A, Size B, Size C).

In Report table the following columns are Size A, Size B, Size C.

In both table the Size A, Size B, Size C columns are common or related.

I am trying to create a rank column in Report table based on the Report table and Data table size (Size A, Size B, Size C). If report table size is lower than or equal to data table size then the Excel Index and max function will return high rank value.

If it has single or multiple matches for same size then max function will give a high rank value. (If there are single or multiple matches for same size it will give a result of Column D (Bin data table).

In Excel, I used Index and Max function in order to get the high rank value based on the Report and data table.

=IF(COUNT(A5:C5)<3,"",MAX(INDEX(DATA!$D$2:$D$13*(DATA!$A$2:$A$13>=$B5)*(DATA!$B$2:$B$13>=$A5)*(DATA!$C$2:$C$13>=$C5),0)))

I am looking for new calculated column option.

Report:

SIZE A	SIZE B	SIZE C	ACTUAL RESULT [RANK]
NA	NA	NA
20000	5000	230	0
400	300	140	12
250	250	160	12
600	400	285	11
400	300	150	12
280	230	170	12
320	320	320	12
320	320	320	12
600	400	140	11
400	300	140	12
400	300	140	12
370	320	340	12

Data:

SIZE A	SIZE B	SIZE C	RANK
420	600	440	11.00
640	600	480	9.00
890	1100	1330	2.00
1335	1100	2350	1.00
890	1100	390	5.00
890	1100	530	3.00
670	1100	540	4.00
670	1100	440	6.00
320	1100	440	10.00
600	400	400	12.00
800	600	400	8.00
1000	600	500	7.00

Report and Data 

[v-robertq-msft]

Hi, @Saxon10 

Have you followed AlexisOlson’s solution to transform your DAX formula and solve this problem?

According to your sample picture, I think this is simply because you have two rows of blank data in the original data table.

I think you can also go to the Power query to filter the data table and click on the “Apply and close” to import the filtered data into the Power BI to solve this problem:

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Saxon10]

@AlexisOlson, and @v-robertq-msft 

I am away a week so unable to respond on time.  Sorry for the late response. 

Thanks for your reply and help.

[mahoneypat]

Here is one way to do it.  Replace RankSize with your table/column name.

RankSize =
VAR thissize = RankSize[SIZE A] * RankSize[SIZE B] * RankSize[SIZE C]
RETURN
    RANKX (
        ALL ( RankSize ),
        RankSize[SIZE A] * RankSize[SIZE B] * RankSize[SIZE C],
        thissize,
        DESC
    )

Pat

[Saxon10]

Thanks for your reply.

I can use your formula in data table (Ranksize) but actually I am looking for help in report table.

I need a formula/DAX code in report table based on the data table rank column and sizes.

The below mantioned Excel formula I am using in report table and I would like to apply the same thing in Power BI. How can I apply the same logic in Power BI? 

Formula in Report table - =IF(COUNT(A5:C5)<3,"",MAX(INDEX(DATA!$D$2:$D$13*(DATA!$A$2:$A$13>=$B5)*(DATA!$B$2:$B$13>=$A5)*(DATA!$C$2:$C$13>=$C5),0)))

Herewith attached Excel and PBI file for your reference.

https://www.dropbox.com/scl/fi/3oaorpmp5hhh115czpivx/INDEX-MAX-ROW-VALUE.xlsx?dl=0&rlkey=o76h2fxhnol...

https://www.dropbox.com/s/2rherjkn89iwl9c/MAX%20AND%20INDEX.pbix?dl=0

[AlexisOlson · Accepted solution]

I think this should replicate your Excel logic.

RankSize =
MAXX (
    FILTER (
        DATA,
        DATA[SIZE A] >= REPORT[SIZE B] &&
        DATA[SIZE B] >= REPORT[SIZE A] &&
        DATA[SIZE C] >= REPORT[SIZE C]
    ),
    DATA[RANK]
)

[Saxon10]

Thanks for your reply. If I have a blanks and NA in report table (Size A, Size B and Size C) then return blanks. I am applying if condition in your exciting DAX but it's giving 12 were sizes contains blanks. Can you please advise.

[AlexisOlson]

Try this for your IF condition:

ISBLANK(REPORT[SIZE A]) && ISBLANK(REPORT[SIZE B]) && ISBLANK(REPORT[SIZE C])

[lbendlin]

"If report table size is lower than or equal to data table size"

how do you define "size"  across the three columns? Are you multiplying the values?

[Saxon10]

Thanks for your reply.

• Yes. I used multiplying the values.