Forum Discussion

johnnyb123's avatar
johnnyb123
New Member
6 years ago
Solved

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
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      johnnyb123
      New Member
      6 years ago

      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's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        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.

         

         

    • johnnyb123's avatar
      johnnyb123
      New Member
      6 years ago

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

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.