Forum Discussion

colacan's avatar
colacan
Resolver II
5 years ago

Selecting rows with highest value within group using virtual table

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.

2 Replies

  • 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]