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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
santhidhanuskod
New Member

TopN records within custom table

Hi,

 

I have created 2 custom tables from the main table using DAX  query.

Active milestones = FILTER('Live Query', ('Live Query'[workflow_stage_name] IN {"In Progress","In Progress - Green","In Progress - Amber","New","In Progress - Red" }))
 
Completed milestones = FILTER('Live Query', AND(
            'Live Query'[workflow_stage_name] = "Completed",
            'Live Query'[planned_due_date].[Date] >= TODAY() - 14))
 
I have a requirement to pick up top 5 records from active milestones + all completed milestones, dispaly in table chart.
I wrote the query as
 
Table = UNION(TOPN(100,'Active milestones', 'Active milestones'[planned_due_date].[Date],ASC),'Completed milestones')
 
But this is not working, it returns only completed milestones. If I pick up only active milestones in table and apply visual filter (top n), then it works....If I try the same in dax, it is not working....
 
please help

 

3 REPLIES 3
MVenables
Advocate I
Advocate I

Hi,

 

Instead of using a TOPN Measure have you considered added a filter to the visual. Advanced filtering then selecting how many by TOPN as per my example below. I have filter by my 'Sell' (Sales Measure)

MVenables_0-1711368577283.png

Thank you

v-jialongy-msft
Community Support
Community Support

Hi @santhidhanuskod 

 

Is your problem solved? If not, can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

 

 

Best Regards,

Jayleny

 

 

 

 

amitchandak
Super User
Super User

@santhidhanuskod , You should create a measure

//change to include the filter from first column

 

M1  = countrows( FILTER('Live Query', AND(
'Live Query'[workflow_stage_name] = "Completed",
'Live Query'[planned_due_date].[Date] >= TODAY() - 14))   )

 

Then have top N Measure

calculate([M1] ,TOPN(3,allselected('Active milestones'[planned_due_date]),[M1]), values('Active milestones'[planned_due_date]))

 

choose the correct columns as needed

 

Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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