Selecting rows with highest value within group using virtual table

colacan — Wed, 25 Nov 2020 15:06:03 GMT

Hi,

Curetnly I have ID (duplicated) column only (Group column does not matter). Starting from this column I want to create a table as below

ID	Group	Rand_Num	Max_Rand_ID
1	A	0.12	0.66
1	B	0.66	0.66
2	A	0.52	0.98
2	A	0.98	0.98
3	B	0.21	0.45
3	A	0.45	0.45
3	A	0.16	0.45

The process I tried is,
1. Generate a 'Rand_Num' column not adding a Colculated column but as DAX variable because I don’t want to increase the file size
2. Create 'Max_Rand by ID' (which is highest Rand_Num within each ID)
3. Iterate the table and filter only Rand() = Max_Rand_ID

I've tried it in many ways but I couldn't achieve the desired reault.

For example,

1        EVALUATE
2        VAR Table_rand =
3            ADDCOLUMNS ( Table1, "Rand_Num", RAND () )
4        VAR Table_rand_filtered =
5            FILTER (
6                Table_rand,
7                VAR Ran_Number = [Rand_Num]
8                RETURN
9                    Ran_Number
10                        = CALCULATE (
11                            MAXX(Table_rand,[Rand_Num]),
12                            ALLEXCEPT ( Table1, Table1[ID] )
13                        )
14            )
15        RETURN
16            Table_rand_filtered

most of error happend between from 10 to12 rows.

It gives either only 1 row-table with highest 'Rand_num' or it gives message that it can't access the [Rand_Num]

It seems it can't access [Rand_Num] whenever I use Calculate() function (row context issue?) while I need it to calculate the highest [Rand_Num] for each ID

For me, it is complecated using vertual table which is always confusing in terms of data lineage and accessing its columns. I will appretiate it if you let me know how to solve this problem. (also any articles I can study handling virtual tables)

Thanks.

Re: Selecting rows with highest value within group using virtual table

amitchandak — Wed, 25 Nov 2020 15:21:51 GMT

colacan , what is the expected output? It is not clear.

Re: Selecting rows with highest value within group using virtual table

colacan — Wed, 25 Nov 2020 15:37:53 GMT

amitchandak

I have ID (duplicated) column only (Group column does not matter). Starting from this column I want to

1. Generate a 'Rand_Num' column not adding a Colculated column but as DAX variable because I don’t want to increase the file size
2. Create 'Max_Rand by ID' (which is highest Rand_Num within each ID)
3. Iterate the table and filter only Rand() = Max_Rand_ID

==> so I will get unique ID table with highest [Rand_Num]

topic Selecting rows with highest value within group using virtual table in DAX Commands and Tips

Selecting rows with highest value within group using virtual table

Re: Selecting rows with highest value within group using virtual table

Re: Selecting rows with highest value within group using virtual table