TopN records within custom table

[santhidhanuskod]

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

[MVenables]

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)

Thank you

[Anonymous]

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]

@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