Hi, Based on the data below I am trying to count the company when the shipping was done the very first time. Company IDCompanyShipped product IDShipped dateFaulty part1Company1101/02/2024 2Company2201/01/2024Yes1Company1310/01/2024Yes2Company2401/02/2024No3Company 3513/01/2024 3Company 3618/01/2024Yes Company 1 will be counted in Jan not in Feb. Likewise, need to count the company when the first faulty part was sent.In this case Company 1 need to be classified into Feb, not Jan. Is it possible to do Ranking on this - Rank based on the Date and then filter by Rank 1?

Hi pbifantic , I am not sure if I understood your requirement. You mentioned that "Likewise, need to count the company when the first faulty part was sent. In this case Company 1 need to be classified into Feb, not Jan.", however, I cannot see "Yes" in the Faulty part column in February for Company 1. With this caveat in mind, to achieve the desired results, you can use DAX formulas in Power BI to rank and filter data based on shipment dates and faulty part shipments. For counting companies based on their first shipment, you can create a calculated column to rank the shipments for each company by date. This can be done using the RANKX function, which will assign a rank to each shipment within the same company, ordered by the shipment date in ascending order. For example, the DAX formula for ranking shipments might look like this: Rank_Shipment = RANKX( FILTER( 'Table', 'Table'[Company ID] = EARLIER('Table'[Company ID]) ), 'Table'[Shipped date], , ASC ) Once you have this rank, you can create a measure to count only the companies whose rank is 1, ensuring you are counting only the first shipment for each company. The measure might look like this: Count_First_Shipment = CALCULATE( DISTINCTCOUNT('Table'[Company ID]), FILTER( 'Table', 'Table'[Rank_Shipment] = 1 ) ) For counting companies based on their first faulty part shipment, a similar approach can be taken. You can create a calculated column to rank only the shipments where the Faulty part column is marked as "Yes". The DAX formula for this ranking would be: Rank_Faulty = IF( 'Table'[Faulty part] = "Yes", RANKX( FILTER( 'Table', 'Table'[Company ID] = EARLIER('Table'[Company ID]) && 'Table'[Faulty part] = "Yes" ), 'Table'[Shipped date], , ASC ) ) Once the rank for faulty shipments is determined, you can create a measure to count companies based on their first faulty shipment. The measure can be written as: Count_First_Faulty = CALCULATE( DISTINCTCOUNT('Table'[Company ID]), FILTER( 'Table', 'Table'[Rank_Faulty] = 1 ) ) In both cases, you can use these measures in your visualizations to display the counts grouped by the Shipped date column aggregated by months. This ensures that each company is counted only once based on either their first shipment or their first faulty shipment. If you need to filter your data table to show only the first shipment or faulty part for each company, you can use a calculated column to mark rows where the rank equals 1 and apply this column as a filter. This approach ensures accurate and clear representation of the data as per the requirements. I have attached an example pbix file. Best regards,

Ranking based on different groupings | Microsoft Fabric Community

Forum Discussion

Related content

Page Level filter for different groups

DAX Ranking by group

Dynamically rank calculated measure based on different groupings

Duplicate ranking

Fabric & Azure Data User Group Program Requirements & Benefits