Forum Discussion

atziovara's avatar
atziovara
Helper I
3 months ago
Solved

Power BI Modeling Question – Dynamic Common Sample + Buckets + Weighted KPIs

🔹 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 m...
"DAX " "DAX help" 'Need help' 'DAX'
"DAX Commands and Tips"
  • johnt75's avatar
    johnt75
    3 months ago

    I think you need to use a calculation group so that you can work out the list of valid companies and then use that as a filter on your chosen measure.

    Create a calculation item like

    Company is in common sample =
VAR _Years =
    TREATAS ( VALUES ( 'Sample Years'[Year] ), 'Date'[Year] )
VAR _NumYears =
    COUNTROWS ( _Years )
VAR _CompaniesYearsAndNumbers =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            Company[Company ID],
            'Date'[Year],
            "@Net sales", [Net sales],
            "@ebitda", [EBITDA],
            "@total assets", [Total assets]
        ),
        REMOVEFILTERS (),
        _Years
    )
VAR _CompaniesYears =
    FILTER (
        _CompaniesYearsAndNumbers,
        [@Net sales] <> 0 && [@ebitda] <> 0 && [@total assets] <> 0
    )
VAR _CompaniesAndNumYears =
    GROUPBY (
        _CompaniesYears,
        Company[Company ID],
        "@num years", SUMX ( CURRENTGROUP (), 1 )
    )
VAR _ValidCompanies =
    SELECTCOLUMNS (
        FILTER ( _CompaniesAndNumYears, [@num years] = _NumYears ),
        Company[Company ID]
    )
VAR Result =
    CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( _ValidCompanies ) )
RETURN
    Result

    and then apply that as a filter to the visuals you want to be affected by your common sample filters.

    The algorithm is relatively straitforward. First you need to calculate the qualifying measures for each company for each year selected. You can then filter out those rows where the qualifying measures are not all non-zero, and count the number of remaining years for each company.

    You only want the companies where the number of valid years matches the number of years selected by the user, and you can use that list of valid companies as an additional filter on top of any other existing filters to calculate the underlying measure.

Juan-Power-bi's avatar
Juan-Power-bi
Super User
3 months ago

I hope it helps

Great problem description. The pattern you need is the same "virtual filter" approach as your Sales Buckets — a disconnected Sample Years table driving a measure that identifies valid companies, which you then apply as a visual-level filter.

Step 1 Disconnected Sample Years table
daxSample Years = DATATABLE("SampleYear", INTEGER, {{2020},{2021},{2022},{2023},{2024},{2025}})
Use this as your slicer (not the main date table).
Step 2  How many sample years are selected
daxSelected Sample Years Count =
COUNTROWS(VALUES('Sample Years'[SampleYear]))
Step 3 Common Sample flag
daxIn Common Sample =
VAR SampleYears = VALUES('Sample Years'[SampleYear])
VAR RequiredYears = COUNTROWS(SampleYears)
VAR CurrentVAT = MAX('Page1'[VAT])

VAR ValidYears =
CALCULATE(
COUNTROWS(
FILTER(
SampleYears,
VAR y = 'Sample Years'[SampleYear]
VAR ns = CALCULATE([Net Sales], 'Page1'[Year] = y, 'Page1'[VAT] = CurrentVAT, ALL('Page1'[Year]))
VAR eb = CALCULATE([EBITDA], 'Page1'[Year] = y, 'Page1'[VAT] = CurrentVAT, ALL('Page1'[Year]))
VAR ta = CALCULATE([Total Assets], 'Page1'[Year] = y, 'Page1'[VAT] = CurrentVAT, ALL('Page1'[Year]))
RETURN NOT ISBLANK(ns) && ns <> 0 && NOT ISBLANK(eb) && eb <> 0 && NOT ISBLANK(ta) && ta <> 0
)
),
ALL('Page1'[Year])
)

RETURN IF(ValidYears = RequiredYears, 1, 0)
Step 4  Apply as visual-level filter
Add In Common Sample = 1 as a visual-level filter on your visuals. Since the Year slicer (your main date axis) is separate from the Sample Years slicer, you can freely analyze across all years while the common sample definition is controlled by Sample Years independently.

  • atziovara's avatar
    atziovara
    Helper I
    3 months ago

    Juan-Power-bi Thank you very much for your reply!

    I am afraid that your solution only works for one single year, for example if I select 2020, my table will return the metrics of companies who have non-zero and non-blank Net Sales, EBITDA, and Total Assets in 2020. If I select in my Sample Years[SampleYear] slicer 2 years or more, all the measures on my table turn blank. The same thing happens if I select any year in my Page1[Financial Year] slicer, therefore I cannot filter the years for which I would like my financial metrics and KPIs to be shown/computed.