Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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 , 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
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
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |