Create a Calculated table with distinct values (rows filtered based on MAX in a column)

[lafakios]

Hi,

I have a table similar to the below

Table_1

ID	Name	Quantity1	Quantity2
1000	1000.a	20	43
1000	1000.b	15	35
1000	1000.c	25	55
1001	1001.a	34	72
1001	1001.b	23	96
1001	1001.c	53	13
1002	1002.a	65	38
1002	1002.b	43	33
1002	1002.c	23	19

and I would like to create a calculated table with the same columns based on the dinstict values of ID.

Every row in the new table should have a dinstict ID value and the values of the other columns of that row which has the highest Quantity2 value. The calculated table should be like the below.

Table_2

ID	Name	Quantity1	Quantity2
1000	1000.c	25	55
1001	1001.b	23	96
1002	1002.a	65	38

By using the below DAX formula, I can only create a table with the dinstic ID and the max Quality2 values.

Is there a way to create a table including also the Name and Quantity1 values?

Table_2 = GENERATE(DISTINCT('Table_1'[ID]),

SUMMARIZE('Table_1',

"Quantity2", CALCULATE(MAX('Table_1'[Quantity2]))))

Thanks in advance for your help.

Regards,

Akis

[Anonymous · Accepted solution]

Hi @lafakios ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new measure in the Table.

Quan2 = 
VAR _MAX = CALCULATE(MAX('Table'[Quantity2]), ALLEXCEPT('Table','Table'[ID]))
RETURN
IF(MAX([Quantity2]) = _MAX, 1, 0)

3.Create the new Table2.

Table 2 = 
SUMMARIZE(FILTER('Table', [Quan2] = 1), 'Table'[ID], 'Table'[Name], 'Table'[Quantity1], 'Table'[Quantity2])

4.The Table 2 visual is shown below.

Best Regards,

Wisdom Wu

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

[lafakios]

Hi,

This solution worked, thank you all for your support!!

Regards,

Akis

[Greg_Deckler]

@lafakios Try:

Table_2 = 
ADDCOLUMNS(
    SUMMARIZE( 'Table_1', [ID], "Quantity2", MAX('Table_1'[Quantity2] )),
    "Quantity1", MAXX(FILTER('Table_1', [ID] = EARLIER([ID]) && [Quantity2] = EARLIER([Quantity2])), [Quantity1]),
    "Name", MAXX(FILTER('Table_1', [ID] = EARLIER([ID]) && [Quantity2] = EARLIER([Quantity2])), [Name])
)

[lafakios]

Thanks for the advice.

I tried the above DAX formula and managed to get a table similar to what I was after.

After doing some investigation, I observed that the generated table does not include all rows as expected.

In other words, the generated table might miss for instance the last row 

"1002 1002.a   65  38"

The investigation I did was based on a comparison done between Table.2 and a Table.1 visual.

The Matrix visual uses in Values measures like the below:

CALCULATE (
SELECTEDVALUE ( 'Table_1'[Quantity1] ),
FILTER ( ALL ( 'Table_1'[Quantity2] ), 'Table_1'[Quantity2] = MAX ( 'Table_1'[Quantity2] ) )