Filtering issue

[pbi_user28]

Hi all,

this may be a stupid question but I did not find a solution to it. I cant filter several rows from a related table even I configured it in a transform step.

Whats needed:

I want to show all activities (by type) in a specified timeframe which are related to incidents but excluding internal incidents (belonging to internal company structure).

What I did so far:

I added the required activity and incident tables from dataverse to a powerbi report and added the relations to all the tables. The relations seem to be ok so far. Then I used the transform function to filter some rows out of the resultset (at least I hope this is the way to do it).
Example:
let
Source = CommonDataService.Database("someinstanceat.dynamics.com"),
dbo_incident = Source{[Schema="dbo",Item="incident"]}[Data],
#"filtered rows" = Table.SelectRows(dbo_incident, each ([accountidname] <> "Internal company")),
#"filtered rows1" = Table.SelectRows(#"filtered rows", each not Text.StartsWith([accountidname], "some other internal company")),
#"filtered rows2" = Table.SelectRows(#"filtered rows1", each ([statecodename] = "Resolved"))
in
#"filtered rows2"

After this we created some measures und visuals to show the amount of hours used. However we still see the hours for internal companies. From what I expected the inner join in the table relation should prohibit this in addition to the filter.

So why I still have the internal hours reported? If I need to provide more details please let me know.

[HotChilli · Accepted solution]

OK, so removing records in Power Query from the Incidents table does not remove them from the other tables.

If you create a visual in powerbi and it uses fields and measures from the other tables then it certainly could contain or include aggregates from 'internal companies'.

--

If the visual contained a field from the Incidents table AND aggregates from tables connected 1:m then the Incidents field acts as a dimension and slices the data to show only appropriate sums/aggregates.  So, in the case of your model, no 'internal company' activities would show.  

Does this make sense?

[pbi_user28]

I think I finally solved it. The solution was to modify the individual activity visuals to contain a filter on incident to remove those where  the incident customer was BLANK.
Explanation:
We have incident table and filtered the incidents with internal companies. The related activity tables are connected through relations to incident table but will contain all activites also those belonging to filtered incidents. However the resulting data will show fields from incident which were filtered as BLANK. So I was able to add another filter to these visuals where the customer field is blank. Then the sum of activities will be lowered as the resultset is filtered now too. This behaviour is a bit strange to me but it seems that this is the way it was designed. (at least to my current knowledge)

Thanks for pointing me to the correct direction.

[HotChilli]

I've seen the model now.  What I would like to see is some evidence of what the problem we are looking at is.

Can you show me a picture with an explanation that reflects this: "created some measures und visuals to show the amount of hours used. However we still see the hours for internal companies."?

[pbi_user28]

I did a check through SQL commands to see the sum of activities which is lower without internal companies. So I identified an incident reported in that timeframe and used that ID to identify the activities for it. Even I filtered those incidents out of the incident table, I can find the activities and times spent in the activity tables. That means the filtering was applied to incident table but the activity tables still contain activities to incidents which should have been filtered out. I am not sure how to show in pictures as its just ID comparing.

[HotChilli]

So you have 2 tables, Activities and Incidents and Activities is a dimension table connected 1:m to the Incidents table?

And the table visual has activity code (or something) and presumably shows 0 hrs for Internal Companies but you don't want to see any 'Internal Company' code in the visual? Is that right?

Maybe a picture would help at this stage. 

[pbi_user28]

Hi this is the relational model

so we have an incident table which holds all incident related data including the customer (as we want to filter everything also by internal customers). there is a second important table which holds the systemusers but its only used to get usernames of support agents.
All other tables are activities the agents may have entered into the system. So we want to sum all activity durations (excluding those for internal customers).
As we have a relational model here I was assuming I can filter incidents which secondarily filters on activities too (as it uses to be for SQL statements). Every activity is connected by 1:n to incidents (one incident can have multiple activities from same type).

[pbi_user28]

thx for the quick reply. I can confirm that in the filtered rows2 I dont have any internal incidents anymore. As the activities are related through the regardingid column I would expect they get filtered too. But as I interpret your question this might be not the case right?
My assumption was that if you filter on a table and you have inner join to other tables they get filtered too.

Within the activities tables I only have the incidentids of incident table and no easy way of filtering.

[HotChilli]

If you examine this table "filtered rows2" in the data view of powerbi front end i.e. not power query,  are there any rows in the table for "internal companies"?

----

Is this the only Fact table which could have internal company data (because I see that you mention relations between tables but we only see one table in the example code)?