Counting values not included in set of filters

[JaveSGE]

I am looking to create a single measure that will switch depending on fields selected within a report, this is to simplify reporting for end users.

To create this single measure I need to create and test the logic of a secondary measure that will count anything that is not counted as part of the measure below. Currently we use the measure below to count opportunities for a specific region and product. When I have simply reversed the criteria it has brought through everything and the totals have wrongly matched [# Opportunity Created Events] and I can only assue this is due to the OR steps within the measure.

(CALCULATE (

            [# Opportunity Created Events],

                dim_campaign[campaign_objective] = ""

                || dim_campaign[campaign_objective] = "NCA"

                || dim_campaign[campaign_objective] = "NCM"

                || dim_campaign[campaign_objective] = "Both",

            dim_pipeline_event[channel_type] = "Direct"

                || dim_pipeline_event[channel_type] = "VAR",

            dim_pipeline_event[channel_creation] = ""

                || dim_pipeline_event[channel_creation] = "Push - Direct",

            dim_pipeline_event[type] = "NEW"

                || dim_pipeline_event[type] = "NEW BUSINESS"

                || dim_pipeline_event[type] = "NEW CUSTOMER",

            dim_pipeline_event[data_source] <> "Salesforce - Global"

            )

    )

My solution to this has been to write the measure below simply minusing the product specific opportunities from the overall opportunity volume. However when doing this, users could not filter on United States, the selected product and a different Campaign_Objective due to the hardcoded nature of the measure.

*** TEST =

IF(

    (SELECTEDVALUE( dim_country[reporting_country_name]) = "United States" && SELECTEDVALUE( dim_product[product_name_reporting]) = "xxx"),

        (CALCULATE (

            [# Opportunity Created Events],

                dim_campaign[campaign_objective] = ""

                || dim_campaign[campaign_objective] = "NCA"

                || dim_campaign[campaign_objective] = "NCM"

                || dim_campaign[campaign_objective] = "Both",

            dim_pipeline_event[channel_type] = "Direct"

                || dim_pipeline_event[channel_type] = "VAR",

            dim_pipeline_event[channel_creation] = ""

                || dim_pipeline_event[channel_creation] = "Push - Direct",

            dim_pipeline_event[type] = "NEW"

                || dim_pipeline_event[type] = "NEW BUSINESS"

                || dim_pipeline_event[type] = "NEW CUSTOMER",

            dim_pipeline_event[data_source] <> "Salesforce - Global"

            )

    )

        ,

        [# Opportunity Created Events] - (

                (CALCULATE (

            [# Opportunity Created Events],

                dim_campaign[campaign_objective] = ""

                || dim_campaign[campaign_objective] = "NCA"

                || dim_campaign[campaign_objective] = "NCM"

                || dim_campaign[campaign_objective] = "Both",

            dim_pipeline_event[channel_type] = "Direct"

                || dim_pipeline_event[channel_type] = "VAR",

            dim_pipeline_event[channel_creation] = ""

                || dim_pipeline_event[channel_creation] = "Push - Direct",

            dim_pipeline_event[type] = "NEW"

                || dim_pipeline_event[type] = "NEW BUSINESS"

                || dim_pipeline_event[type] = "NEW CUSTOMER",

            dim_pipeline_event[data_source] <> "Salesforce - Global"

            )

        )

    )

)

[Anonymous]

Hi,  @JaveSGE 

Based on your description, Because it's not clear what form your data, you can try to solve your problem using the following measures. If it doesn't work, you can share the pbix file without sensitive data or post a complete reproduction of the problem with the data table, measure, desired effect, and the logic to achieve the effect and other information.

Measures:

Opportunities Outside Criteria = 
CALCULATE(
    [# Opportunity Created Events],
    NOT(
        dim_campaign[campaign_objective] IN {"", "NCA", "NCM", "Both"}
        && (dim_pipeline_event[channel_type] IN {"Direct", "VAR"})
        && (dim_pipeline_event[channel_creation] IN {"", "Push - Direct"})
        && (dim_pipeline_event[type] IN {"NEW", "NEW BUSINESS", "NEW CUSTOMER"})
        && (dim_pipeline_event[data_source] <> "Salesforce - Global")
    )
)

Dynamic Opportunities Measure = 
VAR SelectedCountry = SELECTEDVALUE(dim_country[reporting_country_name], "All Countries")
VAR SelectedProduct = SELECTEDVALUE(dim_product[product_name_reporting], "All Products")

RETURN
IF(
    SelectedCountry = "United States" && SelectedProduct = "xxx",
    [# Opportunity Created Events] - [Opportunities Outside Criteria],
    [# Opportunity Created Events]
)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[123abc]

It seems like you're trying to create a measure that dynamically adjusts based on user-selected filters in a report. Your goal is to count opportunities that meet certain criteria and then subtract the count of opportunities meeting specific criteria from the total count of opportunities.

However, it appears that you're encountering issues with the logic and the hardcoded nature of the measures, especially when users apply filters.

To address this, you may consider using dynamic filtering techniques within your DAX measures. One approach is to leverage the DAX functions such as ALLSELECTED, FILTER, and VALUES to dynamically adjust the calculations based on user selections.

Here's a simplified example of how you might structure your measure:

DynamicOpportunityCount =
VAR TotalOpportunities = CALCULATE([# Opportunity Created Events], ALLSELECTED(dim_campaign), ALLSELECTED(dim_pipeline_event))
VAR FilteredOpportunities = CALCULATE([# Opportunity Created Events],
dim_campaign[campaign_objective] IN {"", "NCA", "NCM", "Both"},
dim_pipeline_event[channel_type] IN {"Direct", "VAR"},
dim_pipeline_event[channel_creation] IN {"", "Push - Direct"},
dim_pipeline_event[type] IN {"NEW", "NEW BUSINESS", "NEW CUSTOMER"},
dim_pipeline_event[data_source] <> "Salesforce - Global",
ALLSELECTED(dim_campaign), ALLSELECTED(dim_pipeline_event)
)
RETURN TotalOpportunities - FilteredOpportunities

In this measure:

• TotalOpportunities: Calculates the total count of opportunities disregarding any filters set in the report.
• FilteredOpportunities: Calculates the count of opportunities based on your specified criteria, considering the filters set in the report.
• The RETURN statement subtracts the FilteredOpportunities from the TotalOpportunities to get the count of opportunities not meeting the specified criteria.

This measure should adjust dynamically based on user selections in the report, allowing for more flexible filtering and accurate counts. You can further refine it based on your specific requirements and data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.