Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
colacan
Resolver II
Resolver II

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 2
colacan
Resolver II
Resolver II

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

amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.