🔹 Data Structure

I am working with financial data for companies across multiple years (2020–2025).

The dataset includes:

• Company (VAT Number)
• Financial Year
• Region
• Sector
• Financial metrics:
  • Net Sales
  • EBITDA
  • Total Assets

Example (raw wide format – simplified)

VAT Year Region Sector Net Sales EBITDA Total Assets

🔹 Data Transformation

I have unpivoted the financial columns, so the data in Power BI looks like this:

VAT Year Region Sector Attribute Value

🔹 Base Measures

Net Sales =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "Net Sales")EBITDA =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "EBITDA")Total Assets =CALCULATE(SUM('Page1'[Value]),'Page1'[Attribute] = "Total Assets")

🔹 KPIs

• Weighted Total Asset Turnover = Net Sales / Total Assets
• Weighted EBITDA Margin = EBITDA / Net Sales

🎯 REQUIREMENTS

I want the report to support three independent filtering mechanisms:

1️⃣ Standard Filtering (WORKS)

I can already filter:

• Financial Year
• Region
• Sector

All accounts and KPIs respond correctly.

2️⃣ Sales Buckets (WORKS)

I implemented dynamic sales segmentation:

Step 1 – Bucket definition

Sales Bucket =SWITCH(TRUE(),[Net Sales] < 1000000, "<1M",[Net Sales] >= 1000000 && [Net Sales] < 10000000, "1–10M",[Net Sales] > 10000000, ">10M",[Net Sales] <= 10000000, "<=10M")

Step 2 – Disconnected table

Sales Buckets =DATATABLE("Bucket", STRING,{{"<1M"},{"1–10M"},{">10M"},{"<=10M"}})

Step 3 – Selection logic (multi-select compatible)

Selected Bucket =
VAR NetSales = [Net Sales]
RETURN
IF(
SUMX(
VALUES('Sales Buckets'[Bucket]),
SWITCH(
TRUE(),
'Sales Buckets'[Bucket] = "<1M" && NetSales < 1000000, 1,
'Sales Buckets'[Bucket] = "1–10M" && NetSales >= 1000000 && NetSales < 10000000, 1,
'Sales Buckets'[Bucket] = ">10M" && NetSales > 10000000, 1,
'Sales Buckets'[Bucket] = "<=10M" && NetSales <= 10000000, 1,
0
)
) > 0,
1,
0
)

Step 4 – Applied as filter

I apply:

Selected Bucket = 1

and both sums and KPIs work correctly.

3️⃣ Common Sample (NOT WORKING)

This is the main issue.

🎯 Goal

I want a slicer that lets the user define a set of years (Sample Years).

Then:

👉 A company belongs to the Common Sample if:

• It has non-zero and non-blank
  • Net Sales
  • EBITDA
  • Total Assets
• in ALL selected Sample Years

✔ Example

If user selects:

👉 Sample Years = {2022, 2023}

Then:

• Company A → ❌ excluded (has 0 in 2023)
• Company B → ✔ included
• Company C → ❌ excluded (missing 2021 irrelevant, but 2022 ok → depends only on selected years)
• Company D → ✔ included
• Company E → ❌ excluded (no data in selected years)

🔴 Important Requirement

Once a company is included in the Common Sample:

👉 We must be able to analyze it across ALL years (e.g. 2020–2025)

—not only the selected sample years.

❗ Problem

I attempted multiple approaches using:

• measures (COUNT / FILTER / SUMX)
• disconnected tables (Sample Years)
• TREATAS
• APPLY FILTER logic similar to Sales Buckets

However:

• Results either return all companies
• or all BLANK values
• or do not respond to Sample Year slicer
• or create circular dependency errors when trying calculated tables

❓ QUESTION

What is the correct modeling approach in Power BI to implement:

👉 A dynamic common sample filter (based on multiple selected years)

that:

1. Filters companies based on validity across selected years
2. Works together with other filters (Year, Region, Sector, Sales Bucket)
3. Still allows analysis across all years
4. Works correctly with aggregated measures and weighted KPIs

Any guidance on proper DAX pattern or data modeling approach would be greatly appreciated! 😄