Inverting Slicer output to a table

[johnnyb123]

Hey all,

New to power bi and DAX and have come up with an issue i can not seem to solve.

I have two tables Computers and Applictions.

Applications contains [ComputerID] and [Name].

Computers contains [ComputerID] and [HostName].

Relationship is established as a one to many from 'Computers'[ComputerID] to 'Applications'[ComputerID]

I want to use an Applications[Name] slicer to show computers that do not have it installed.

I have created another table with just 'Application'[Names] in it called Apps

this is what i have tried
(This returns an error "cannot find Computers[Name] or no relationship exsists" and it does)

NoInstalled =

var selected = SELECTEDVALUE(Apps[Name],"Not Filtered")

return

IF(ISFILTERED(Apps[Name]),

CALCULATE(

CONCATENATE(RELATED(Computers[Name]),""),

Applications[Name] <> selected

),

"Not Filtered"

)

and

NoInstalled =

var selected = SELECTEDVALUE(Apps[Name],"Not Filtered")

return

IF(ISFILTERED(Apps[Name]),

CALCULATE(

CONCATENATEX(Computers,Computers[ComputerId],","),

Applications[Name] <> selected

),

"Not Filtered"

)

Tried this one just to see if the filter works as intended and it does. Obvioulsy it is just spitting out one row/column with one long string.

Ive seen a couple of articles about inverting sums and the like but not this.
Any heko would be greatly apreciated.

Thank You

[v-alq-msft · Accepted solution]

Hi, @johnnyb123 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Applications:

Computers:

Apps(a calculated table):

Apps = DISTINCT(Applications[Name])

You may create a measure as below.

Visual Control = 
IF(
    ISFILTERED(Apps[Name]),
    IF(
        COUNTROWS(
            FILTER(
                DISTINCT(Apps[Name]),
                NOT(Apps[Name] in DISTINCT(Applications[Name]))
            )
        )=
        COUNTROWS(
            DISTINCT(Apps[Name])
        ),
        1,0
    ),
    1
)

Then you need to put the measure in the visual level filter and use the 'Name' column from 'Apps' table to filter the result.

Best Regards

Allan

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

[johnnyb123]

This worked great! Thanks a bunch @v-alq-msft !

[parry2k]

@johnnyb123 you don't need concatenate but just a measure 

NoInstalled =
var selected = SELECTEDVALUE(Apps[Name],"Not Filtered")
return
IF(ISFILTERED(Apps[Name]),
CALCULATE(
COUNTROWS ( Computers ),
CROSSFILTER( Applications[ComputerId], Computer[ComputerId], Both ),
Applications[Name] <> selected
))

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[johnnyb123]

wanted to give you a koodos because it works and i can use it in another project!

[johnnyb123]

Thanks for the response @parry2k . I think i need to be just a tad more clear. I need the [HostName] of each computer to show as the value of a row in a table visual. The value would be a hostname record that does not have a corresponding [ComputerID] when filtered by Application[Name]. If there are 30 records after the filter is applied, there should be 30 rows in a necolumn table visual with [Hostname] as the value.

Again Thank you for helping.

[parry2k]

@johnnyb123 let's make it super simple and clear, paste sample data and expected output, it will help to understand what you are trying to achieve.