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.
