cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Inverting Slicer output to a table

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Inverting Slicer output to a table

Hi, @johnnyb123 

 

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

 

Applications:

e1.png

 

Computers:

e2.png

 

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.

e4.png

 

e3.png

 

Best Regards

Allan

 

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

 

View solution in original post

6 REPLIES 6
Highlighted
Super User VII
Super User VII

Re: Inverting Slicer output to a table

@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.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





New Member

Re: Inverting Slicer output to a table

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.

Highlighted
Super User VII
Super User VII

Re: Inverting Slicer output to a table

@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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Community Support
Community Support

Re: Inverting Slicer output to a table

Hi, @johnnyb123 

 

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

 

Applications:

e1.png

 

Computers:

e2.png

 

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.

e4.png

 

e3.png

 

Best Regards

Allan

 

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

 

View solution in original post

Highlighted
New Member

Re: Inverting Slicer output to a table

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

Highlighted
New Member

Re: Inverting Slicer output to a table

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors