How to Filter DAX Measure in CALCULATE Based on Another Measure's Value

[user_guddu10]

I'm developing a Power BI report to analyze environmental data by counting distinct companies based on specific criteria. I have several DAX measures and a custom category table involved in this setup. Below is a brief overview of each component:

Count Measures:

1. Reference_Asset_Count_Rev: Counts distinct companies where emissions are greater than zero, revenue is at least $1 million, and both emissions and revenue are not blank.

Reference_Asset_Count_Rev =  
CALCULATE(
    DISTINCTCOUNT('ESG_Assets_Data_Pivot'[company_name]),
    'ESG_Assets_Data_Pivot'[Total Emissions] > 0,
    'ESG_Assets_Data_Pivot'[revenue_usd] >= 1000000,
    NOT ISBLANK('ESG_Assets_Data_Pivot'[Total Emissions]),
    NOT ISBLANK('ESG_Assets_Data_Pivot'[revenue_usd])
)

2. Reference_Count_By_Year_Rev: Calculates the count of distinct companies (as defined in Reference_Asset_Count_Rev) for each individual reporting year.

Reference_Count_By_Year_Rev = 
CALCULATE(
    [Reference_Asset_Count_Rev],
    VALUES(ESG_Assets_Data_Pivot[report_year])
)

3. Combined_Asset_Count_Rev: Returns counts based on selected portfolio categories using a SWITCH function, depending on the selection in the Combined_Category table.

Combined_Asset_Count_Rev = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Combined_Category[Category]) = "Selected Portfolio", [Reference_Count_By_Year_Rev],
    SELECTEDVALUE(Combined_Category[Category]) = "Benchmark", [Benchmark_Count_By_Year_Rev],
    SELECTEDVALUE(Combined_Category[Category]) = " Benchmark X", [Benchmark_X_Count_by_year_Rev],
    BLANK()
)

Custom Table:

Combined_Category: A custom table created for categorizing assets into different portfolios for analysis in reports and visuals. It facilitates selection and sorting based on predefined categories.

Combined_Category = 
DATATABLE(
    "Category", STRING,
    "Sort Order", INTEGER,
    {
        {"Selected Portfolio", 1},
        {"Benchmark", 2},
        {" Benchmark X", 3}  // Note the leading space in " Benchmark X" might be a typo unless specifically intended.
    }
)

I also have a calculated DAX measure called 'Average Emissions Per Revenue'.:

Average Emissions Per Revenue (per mil) by Year = 
AVERAGEX(
    SUMMARIZE(
        ESG_Assets_Data_Pivot,
        ESG_Assets_Data_Pivot[company_name],
        ESG_Assets_Data_Pivot[report_year],
        "Total Emissions", [Total Emissions Latest Survey Year],  // Reference the existing measure
        "Total Revenue", [Total Revenue Latest Survey Year]  // Reference the existing measure
    ),
    IF(
        DIVIDE(
            [Total Emissions],
            [Total Revenue] / 1000000,
            0
        ) >= 0.1,
        DIVIDE(
            [Total Emissions],
            [Total Revenue] / 1000000,
            0
        ),
        BLANK()  // or 0, if you prefer to count these as zero in your average
    )
)

I want to avoid creating a custom column for this due the number conditions I have set in the formula above. 

Below is my current matrix table is below along with it's visualizations pane:

Q. Given this setup, how can I include a condition in my counting measures to only count "ESG_Assets_Data_Pivot'[company_name]" where the "Average Emissions Per Revenue (per mil) by Year" exceeds 0.1?

I attempted to directly use this average measure in a CALCULATE filter expression but encountered an error stating that a function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression, which is not allowed.

Is there a recommended approach or alternative method to achieve this filtering without running into the mentioned issue?

Thank you in advance for your insights and suggestions!

[Kedar_Pande · Accepted solution]

@user_guddu10 

Updated Measure:

Filtered_Asset_Count = 
CALCULATE(
DISTINCTCOUNT('ESG_Assets_Data_Pivot'[company_name]),
FILTER(
SUMMARIZE(
ESG_Assets_Data_Pivot,
ESG_Assets_Data_Pivot[company_name],
ESG_Assets_Data_Pivot[report_year],
"AvgEmissionsPerRevenue", 
DIVIDE(
[Total Emissions Latest Survey Year], 
[Total Revenue Latest Survey Year] / 1000000, 
0
)
),
[AvgEmissionsPerRevenue] > 0.1
)
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[user_guddu10]

Thank you so much. This worked perfectly. I have another similar issue I am trying to fix.

For context, I have 2 slicer panes, one is called reference selections and one is benchmark selections. Benchmark selections is pulling data from duplicate tables being used in reference selections. I am using the userelationship function to ensure the slicers are seperated.

- Below is how I calculated Benchmark_Asset_Count_Revenue:

Benchmark_Asset_Count_Rev = 
CALCULATE(
    DISTINCTCOUNT('Bridge_Assets'[dv_company_name]),
    ALL('OMNI_Portfolios'),
    ALL('v_sira_omni_investments'),
    ALL('ESG_Assets_response_mapping'),
    USERELATIONSHIP('ESG_Assets_response_mapping_2'[ri_asset_id], Bridge_Assets[ri_asset_id]),
    'ESG_Assets_Data_Pivot'[Total Emissions] > 0,
    'ESG_Assets_Data_Pivot'[revenue_usd] >= 1000000,
    VAR AvgEmissionsPerRevenue = DIVIDE(
        'ESG_Assets_Data_Pivot'[Total Emissions], 
        'ESG_Assets_Data_Pivot'[revenue_usd] / 1000000, 
        0
    )
    RETURN AvgEmissionsPerRevenue > 0.1
)

Now I am trying to apply the same logic for NAV (Net Asset Value).

- Below is how I calculated Reference_Asset_Count_NAV

Reference_Asset_Count_NAV = 
CALCULATE(
    DISTINCTCOUNT('Merge_NAV'[dv_company_name]),
    FILTER(
        SUMMARIZE(
            'Merge_NAV',
            'Merge_NAV'[dv_company_name],
            'Merge_NAV'[report_year],
            "AvgEmissionsPerNAV", 
            DIVIDE(
                [Total Emissions Latest Survey Year], 
                [Total NAV Latest Survey Year] / 1000000,
                0
            )
        ),
        [AvgEmissionsPerNAV] > 0.1
    )
)

-  Calculation for Average Emissions Per NAV (per mil) by Year

Average Emissions Per NAV (per mil) by Year = 
AVERAGEX(
    SUMMARIZE(
        Merge_NAV,
        Merge_NAV[dv_company_name],
        Merge_NAV[report_year],
        "Total Emissions", [Total Emissions Latest Survey Year],  // Reference the existing measure
        "Total NAV", [Total NAV Latest Survey Year]  // Adjusted to use the NAV measure
    ),
    IF(
        DIVIDE(
            [Total Emissions],
            [Total NAV] / 1000000,
            0
        ) >= 0.1,
        DIVIDE(
            [Total Emissions],
            [Total NAV] / 1000000,
            0
        ),
        BLANK()  // or 0, if you prefer to include these as zero in the average
    )
)

 
Q. I am now trying to calculate Benchmark_Asset_Count_NAV. I have tried using 2 formulas for this:

1. (With this formula, the count results are correct but the reference slicers are filtering the benchmark count as well)

Benchmark_Asset_Count_NAV = 
CALCULATE(
    DISTINCTCOUNT('Merge_NAV'[dv_company_name]),
    ALL('OMNI_Portfolios'),
    ALL('v_sira_omni_investments'),
    ALL('ESG_Assets_response_mapping'),
    USERELATIONSHIP('ESG_Assets_response_mapping_2'[ri_asset_id], 'Bridge_Assets'[ri_asset_id]),
    FILTER(
        SUMMARIZE(
            'Merge_NAV',
            'Merge_NAV'[dv_company_name],
            'Merge_NAV'[report_year],
            "AvgEmissionsPerNAV", 
            DIVIDE(
                [Total Emissions Latest Survey Year], 
                [Total NAV Latest Survey Year] / 1000000,
                0
            )
        ),
        [AvgEmissionsPerNAV] > 0.1
    )
)

2. (With this formula, the count results are incorrect but the reference slicers are not filtering the benchmark count)

Benchmark_Asset_Count_NAV = 
CALCULATE(
    DISTINCTCOUNT('Merge_NAV'[dv_company_name]),
    ALL('OMNI_Portfolios'),
    ALL('v_sira_omni_investments'),
    ALL('ESG_Assets_response_mapping'),
    USERELATIONSHIP('ESG_Assets_response_mapping_2'[ri_asset_id], Bridge_Assets[ri_asset_id]),
    'ESG_Assets_Data_Pivot'[Total Emissions] > 0,
    'Merge_NAV'[NAV_usd] >= 1000000
)

How can I modify my DAX formula or model to ensure that reference slicers do not affect the benchmark calculation and I am getting the count for AvgEmissionsPerNAV > 0.1?

[Kedar_Pande]

@user_guddu10 

Corrected Formula for Benchmark_Asset_Count_NAV

Benchmark_Asset_Count_NAV = 
CALCULATE(
DISTINCTCOUNT('Merge_NAV'[dv_company_name]),
ALL('OMNI_Portfolios'), 
ALL('v_sira_omni_investments'), 
ALL('ESG_Assets_response_mapping'), 
USERELATIONSHIP('ESG_Assets_response_mapping_2'[ri_asset_id], 'Bridge_Assets'[ri_asset_id]),
FILTER(
SUMMARIZE(
'Merge_NAV',
'Merge_NAV'[dv_company_name],
'Merge_NAV'[report_year],
"AvgEmissionsPerNAV",
DIVIDE(
[Total Emissions Latest Survey Year], 
[Total NAV Latest Survey Year] / 1000000, 
0
)
),
[AvgEmissionsPerNAV] > 0.1
)
)

Ensure all slicer-related tables (affecting references) are cleared using ALL.
Keep USERELATIONSHIP active for proper benchmark filtering.
Test with different slicer combinations to confirm the reference slicers do not affect the benchmark counts.

[user_guddu10]

I have adjusted the formula. I have duplicated the table Merge_NAV and made an inactive realtionship. It is working fine now. However, if emissions is 0, it is not taking into account those calculations to remove them. For example, if NAV = 0 and emissions = 2000, the formula below will not count this. However, if NAV = 2000 and emissions = 0, it is still counting these. 

Benchmark_Asset_Count_NAV = 
CALCULATE(
    DISTINCTCOUNT('Merge_NAV_2'[dv_company_name]),
    ALL('OMNI_Portfolios'),  // Clears filters on the 'OMNI_Portfolios' table
    ALL('v_sira_omni_investments'),  // Clears filters on the 'v_sira_omni_investments' table
    ALL('ESG_Assets_response_mapping'),  // Clears filters on the 'ESG_Assets_response_mapping' table
    USERELATIONSHIP('ESG_Assets_response_mapping_2'[ri_asset_id], 'Bridge_Assets'[ri_asset_id]),
    USERELATIONSHIP('ESG_Assets_Data_Pivot'[ri_response_assets_id], 'Merge_NAV_2'[ri_response_assets_id]),
    FILTER(
        SUMMARIZE(
            'Merge_NAV_2',
            'Merge_NAV_2'[dv_company_name],
            'Merge_NAV_2'[report_year],
            "AvgEmissionsPerNAV",  // Still calculating average emissions per NAV for possible future use or reporting
            DIVIDE(
                [Benchmark Total Emissions], 
                [Total NAV_2 Latest Survey Year] / 1000000,
                0  // Ensures division by zero returns 0 instead of an error
            )
        ),
        [Total NAV_2 Latest Survey Year] > 0 &&  // Ensures that NAV is greater than zero
        [Benchmark Total Emissions] > 0.1 &&  // Ensures that Total Emissions are greater than 0.1
        NOT(ISBLANK([Benchmark Total Emissions]))  // Ensures that Total Emissions are not blank
    )
)

[user_guddu10]

Even after using this formula I am getting the same issue where the count results are correct but the reference slicers are filtering the benchmark count as well.

For example, I have chosen a slicer from reference selection only, but it is filtering for both selected portfolio and benchmark. It should only filter Selected Portfolio. 

The result of choosing from only a bechmark selection slicer is below:

[bhanu_gautam]

@user_guddu10 To include a condition in your counting measures to only count ESG_Assets_Data_Pivot'[company_name] where the Average Emissions Per Revenue (per mil) by Year exceeds 0.1, you can use a combination of SUMMARIZE and FILTER within your CALCULATE function. 

Reference_Asset_Count_Rev =
CALCULATE(
DISTINCTCOUNT('ESG_Assets_Data_Pivot'[company_name]),
'ESG_Assets_Data_Pivot'[Total Emissions] > 0,
'ESG_Assets_Data_Pivot'[revenue_usd] >= 1000000,
NOT ISBLANK('ESG_Assets_Data_Pivot'[Total Emissions]),
NOT ISBLANK('ESG_Assets_Data_Pivot'[revenue_usd]),
FILTER(
SUMMARIZE(
'ESG_Assets_Data_Pivot',
'ESG_Assets_Data_Pivot'[company_name],
'ESG_Assets_Data_Pivot'[report_year],
"AvgEmissionsPerRevenue",
AVERAGEX(
SUMMARIZE(
'ESG_Assets_Data_Pivot',
'ESG_Assets_Data_Pivot'[company_name],
'ESG_Assets_Data_Pivot'[report_year],
"Total Emissions", [Total Emissions Latest Survey Year],
"Total Revenue", [Total Revenue Latest Survey Year]
),
IF(
DIVIDE(
[Total Emissions],
[Total Revenue] / 1000000,
0
) >= 0.1,
DIVIDE(
[Total Emissions],
[Total Revenue] / 1000000,
0
),
BLANK()
)
)
),
[AvgEmissionsPerRevenue] > 0.1
)
)

[user_guddu10]

Thank you for your time and effort. Unfortunately, this was giving a lot of errors.