Forum Discussion
PowerBI Model not Filtering as Expected
Hi,
Not too sure where i'm going wrong here, i've built a fairly basic model, and connected both data tables (Event Details) and (Opportunities) to a lookup table for Segments which uses a "unique segment key column" to connect the three tables.
From this i'm trying to build a drill down report by Event Year, Event Code, Budget Segment, Salesforce Segment then by Opportunity name, however when I get to drill by opportunity name the connection is lost and all opportunities appear to be showing instead of just those opportunities that filter into the Event Code, Budget Segment, Salesforce Segment etc.
ninashand , As the opportunity name comes from a table at the same level as event details, you might not see the correct result in that case. Prefer a drill-through a report or tooltip report that do not use event details measures
If it's the same value that using on [Segment Target] and [Event Detail], you might just pick those field from [Segment Target] instead.
If Hierarchy Filter is trying to build hierarchy line under [Event] > [Segement Target] >[Opportunity] only.
It's logical to have subset of opportunity value follow the hierarchy.
However, If Hierarchy Filter is trying to build hierarchy line under [Event] > [Segement Target] > [Event Detail] >[Opportunity]. (as you told the visual to do, by pulling [Event Detail] fields into it).
There the linkage of [Event Detail] > [Opportunity] just will happen as "open join", ie, it join every [Event Detail] to every [Opportunity].
This explained your unwanted result.
6 Replies
If it's the same value that using on [Segment Target] and [Event Detail], you might just pick those field from [Segment Target] instead.
If Hierarchy Filter is trying to build hierarchy line under [Event] > [Segement Target] >[Opportunity] only.
It's logical to have subset of opportunity value follow the hierarchy.
However, If Hierarchy Filter is trying to build hierarchy line under [Event] > [Segement Target] > [Event Detail] >[Opportunity]. (as you told the visual to do, by pulling [Event Detail] fields into it).
There the linkage of [Event Detail] > [Opportunity] just will happen as "open join", ie, it join every [Event Detail] to every [Opportunity].
This explained your unwanted result.ninashand , are you using something from event details too?
amitchandak i'm only using values from Event Details, all the values are displaying correctly and rolling up correctly, for both event details values and opporunity values
ninashand , as event details values and opportunity are at the same level, that might be causing it.
can you share the column details with table name you have used in visual
The Target columns are coming from Event_Details and the actuals (no. of exhibitors, total sqm, space only) are coming from Opportunity the amounts are rolling up and are correct, however when I go to show the opportunity name as a row, it gives me all opptys instead of just the opportunities in that segment with a value, the opportunities which don't relate have no value in the value so the calculation is still correct but it is not easy to scroll through all opportunities to find the ones with values
