Top 2 docs to be displayed

[emerald60]

Docname	Region	Date	Month
ABC1235	USA	20241001	202410
ABC1236	USA	20241002	202410
ABC1237	USA	20241003	202410
ABC1238	USA	20240901	202409
ABC1239	USA	20240902	202409
ABC1240	UK	20240903	202409
ABC1241	UK	20240807	202408
ABC1242	UK	20240808	202408
ABC1243	UK	20240809	202408
ABC1244	UK	20240810	202408
ABC1245	UK	20240811	202408
ABC1246	Europe	20240812	202408
ABC1247	Europe	20240813	202408
ABC1248	Europe	20240814	202408

I have a selection of month 

if I select august: 202408:   then top 2 Docs to be displayed in that selection by date 
If select  Sep:  202409: again top2 in that sep month
top2 materials to be displayed in selected month? please help?

[Kedar_Pande · Accepted solution]

@emerald60 

Create a DAX Measure

Top 2 Docs = 
VAR SelectedMonth = MAX('Table'[Month]) 
VAR TopDocs = 
TOPN(
2, 
FILTER(
'Table', 
'Table'[Month] = SelectedMonth 
),
'Table'[Date], 
DESC
)
RETURN
COUNTROWS(TopDocs)

Place the Docname, Date, and Month fields in a Table visual.
Add the Top 2 Docs measure to the visual.

You can use the Filter pane to limit the visual to only the top 2 rows based on the Top 2 Docs measure, or
Add a Conditional Column to determine which rows to display based on the DAX logic.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[DataNinja777 · Accepted solution]

@emerald60 ,

Using a calendar table is considered a best practice for time intelligence calculations in Power BI, as it ensures consistency, simplifies DAX formulas, and enables better filtering. Here is how you can integrate a calendar table into your model and implement the solution to display the top two documents for the selected month.

First, create a calendar table in Power BI using DAX. This table should cover the range of dates in your dataset. You can write the following formula to create it:

Calendar = 
ADDCOLUMNS(
    CALENDAR(MIN('Table'[Date]), MAX('Table'[Date])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "YYYYMM"),
    "MonthName", FORMAT([Date], "MMMM"),
    "Day", DAY([Date])
)

Next, establish a relationship between the Date column in your table and the Date column in the newly created Calendar table. This ensures that the calendar table acts as the primary reference for time-based filtering. Use the Calendar table for slicers, such as the one for selecting a month.

Define a measure to rank documents by their date within the selected month. The DAX formula for this measure is:

Rank By Date = 
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[Month] = MAX('Calendar'[Month])
    ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

Create another measure to filter only the top two documents based on their rank. The formula for this measure is:

Top 2 Docs = 
IF(
    [Rank By Date] <= 2,
    1,
    0
)

To visualize the data, add the columns Docname, Region, and Date from your table into a table visual. Add the Month column from the Calendar table to a slicer to enable the selection of the desired month. Apply a filter to the table visual to display rows where the measure Top 2 Docs equals one.

The use of a calendar table in this solution provides centralized time filtering, ensuring consistency across visuals and calculations. It also improves performance by clarifying the filter context in DAX formulas and allows for flexibility in time-based calculations, such as year-to-date or month-over-month comparisons.

For example, selecting the month 202408 in the slicer will display the following data:
Docname: ABC1241, Region: UK, Date: 20240807
Docname: ABC1242, Region: UK, Date: 20240808

Similarly, selecting the month 202409 will display:
Docname: ABC1238, Region: USA, Date: 20240901
Docname: ABC1239, Region: USA, Date: 20240902

This approach adheres to best practices by leveraging a calendar table, ensuring a robust and scalable reporting model.

Best regards,

[Kedar_Pande · Accepted solution]

@emerald60 

Create a DAX Measure

Top 2 Docs = 
VAR SelectedMonth = MAX('Table'[Month]) 
VAR TopDocs = 
TOPN(
2, 
FILTER(
'Table', 
'Table'[Month] = SelectedMonth 
),
'Table'[Date], 
DESC
)
RETURN
COUNTROWS(TopDocs)

Place the Docname, Date, and Month fields in a Table visual.
Add the Top 2 Docs measure to the visual.

You can use the Filter pane to limit the visual to only the top 2 rows based on the Top 2 Docs measure, or
Add a Conditional Column to determine which rows to display based on the DAX logic.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Selva-Salimi]

Hi @emerald60 

how did you evaluate the top ones?? is that based on the dates?!