Filter on Application not installed on a specific device

[cybertunnel]

So I have broken down my dataset to a simple setup for argument's sake.

So I have a list of devices in 'Devices' which has the [id], [name]. I have another table 'Applications' which has the [device id], [app name]. The problem I am having is filtering on devices which DON'T have application 'Microsoft Power BI'. I can find devices which DO have it, but can't figure out how to filter on just devices which don't have the specified app.

Thoughts?

[VahidDM]

Hi @cybertunnel 

try this filter:

FILTER(Applications,Applications[app name]<>"Microsoft Power BI")

If you share a sample of your data and result in a table format, that will help to prepare a better solution.

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

Appreciate your Kudos!!

[cybertunnel]

I actually noticed the issue with my communication, sorry for that.

The filter your purposed, does work, but I guess my filtering is wrong, or something is flawed.

The problem I have is the Applications table returns rows which have the name that doesn't match 'Microsoft Power BI' which might be a device with 'Microsoft Excel'. This problem allows a device which has Power BI installed, but is grabbed by the filter since there is an entry with "Microsoft Excel".

Hope that helps shed the light on the issue I am having. The filter on the table relationship is in both directions on the Device's ID, just an FYI.

[cybertunnel]

I was hoping for more of a filter option able to be used in a report. I was even thinking of a measure if really needed.

The dataset I have has some sensitive information, how would I go about making a table for reference for you?

[VahidDM]

Hi @cybertunnel 

Create a sample table with some data not real data (5 or 6 rows are enough if that cover your request?

More important, create a result table to be clear what are you looking for?

Appreciate your Kudos!!

[cybertunnel]

Ok, so here are some an example Dataset below. So using the dataset below, I have 5 devices with a range of different applications.

So I have 5 total devices, and if I say I want to grab the total of the below that DO have Power BI, I get 3, but if I say that DON'T have Power BI, I still get 5. I have cross filtering on the datasets as well, and my Measure is counting the table rows in Devices.

I also have 4 devices which have Outlook, but when I ask for devices which don't have Outlook it returns 5 devices not 1.

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZLLCsIwEEV/pWTtxiR+gC/EhbRQ0UXporbRBtOO5KF+vrYFX8y4nHsS5lyYLGNjNmIbXVpwcPRRHLwBOLN89EuW91IZJE/gpuwFdOsRuAdbIfFWFY1D8rhVC6uv6oVS/3zZT5zU5P90ONGB45qc0ER2RLM1JvbZoEMrgJNR0by20AypIJsIQlbQ2wXeQxA9BC3boZ12oTBR6kOlYfghSVtJ2EraVuK2krb6RtOyVA7zSsLBaFcr27PJ+3LyBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device ID" = _t, Application = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Device ID", Int64.Type}, {"Application", type text}})
in
#"Changed Type"```

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrJz40pNTAwMi9W8EksKMkvUIrViVYyAsq4JJZlpsDkXFKLs2GSxkDJ4MScnEqYZHBpUVpicipY0gQo6ZNfVJSYmZeKxWBToLRvYmaOQnBqUVlqkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Device ID" = _t, #"Device Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Device ID", Int64.Type}, {"Device Name", type text}})
in
#"Changed Type"```

[v-yiruan-msft · Accepted solution]

Hi @cybertunnel ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below to get the count of devices which don't have the special character:

Count of devices which don't have = 
VAR _searchtext = "Outlook"
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Applications'[Device ID] ),
        CONTAINSSTRING ( 'Applications'[Application], _searchtext )
    )
VAR _tab2 =
    CALCULATETABLE (
        VALUES ( 'Applications'[Device ID] ),
        NOT ( CONTAINSSTRING ( 'Applications'[Application], _searchtext ) )
    )
RETURN
    COUNTROWS ( EXCEPT ( _tab2, _tab ) )

Best Regards

[cybertunnel]

Thank you! This works!