Forum Discussion

Advocate I

1 year ago

Solved

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

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 setu...

Kedar_Pande

1 year ago

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

Kedar_Pande

Super User

1 year ago

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

Advocate I

1 year ago

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

Super User

1 year ago

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
Advocate I
1 year ago
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:

user_guddu10

Advocate I

1 year ago

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
    )
)

Forum Discussion

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

Related content

DAX Calculated Measure

Measure or Calculated Column

DAX Measure

Measure

Path Measure