Adjusting DAX Formulas for Independent Slicer Functionality in Power BI

[user_guddu10]

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 Reference_Asset_Count_Revenue:

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

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

[user_guddu10]

Thank you for your reply.

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:

[BeaBF]

@user_guddu10 Hi! try with:

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

BBF

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