Forum Discussion

santhidhanuskod's avatar
santhidhanuskod
Regular Visitor
2 years ago

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

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

     

     

     

     

  • MVenables's avatar
    MVenables
    Advocate II
    2 years ago

    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