Prevent filters from impacting a virtual table inside a measure

[mark_carlisle]

We have created some measures which appeared to work as seen in the screenshot below.

However when adding further fields these measures "break", note the lack of values for # New MQL's Converted to Opportunity and # New MQL's Converted to Closed Won.

The DAX for these measures is

# New MQL's Converted to Opportunity = 
VAR __table =
    SUMMARIZE (
        fact_pipeline_events,
        fact_pipeline_events[pipeline_journey_id],
        "@has_events",
            [# New MQL's] > 0
                && [# Opportunity Created Events] > 0
    )
VAR __filtered_table =
    FILTER ( __table, [@has_events] = TRUE () )
VAR __journey_id_table =
    SUMMARIZE ( __filtered_table, fact_pipeline_events[pipeline_journey_id] )
VAR __result =
    CALCULATE ( [# New MQL's], __journey_id_table )
RETURN
    __result

# New MQL's Converted to Closed Won = 
VAR __table =
    SUMMARIZE (
        fact_pipeline_events,
        fact_pipeline_events[pipeline_journey_id],
        // dim_pipeline_event[stage_name],
        "@has_events",
            [# New MQL's] > 0
                && [# Stage Name Change Events] > 0
    )
VAR __table_filtered =
    FILTER ( __table, [@has_events] = TRUE () )
VAR __table_with_stage_name =
    SUMMARIZE (
        fact_pipeline_events,
        fact_pipeline_events[pipeline_journey_id],
        dim_pipeline_event[stage_name]
    )
VAR __table_with_stage_name_closed_won =
    FILTER (
        __table_with_stage_name,
        dim_pipeline_event[stage_name] = "Closed Won"
    )
VAR __joined_tables =
    NATURALINNERJOIN ( __table_filtered, __table_with_stage_name_closed_won )
VAR __journey_id_table =
    SUMMARIZE ( __joined_tables, fact_pipeline_events[pipeline_journey_id] )
VAR __result =
    CALCULATE ( [# New MQL's], ALL (fact_pipeline_events), __journey_id_table )
RETURN
    __result

So my question is how do we stop the filters that are applied by adding the columns to the visual from impacting the result of the measure?

[amitchandak · Accepted solution]

@mark_carlisle , use removefilters to your measure for those additional columns

Learn Power BI: All, allselected, removefilters,allexcept- https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[mark_carlisle]

Thanks, we appear to have got there in the end by wrapping the fact_pipeline_events table in ALLSELECTED().

# New MQL's Converted to Opportunity = 
VAR __table =
    SUMMARIZE (
        ALLSELECTED ( fact_pipeline_events ),
        fact_pipeline_events[pipeline_journey_id],
        "@has_events",
            [# New MQL's] > 0
                && [# Opportunity Created Events] > 0
    )
VAR __filtered_table =
    FILTER ( __table, [@has_events] = TRUE () )
VAR __journey_id_table =
    SUMMARIZE ( __filtered_table, fact_pipeline_events[pipeline_journey_id] )
VAR __result =
    CALCULATE ( [# New MQL's], __journey_id_table )
RETURN
    __result