Forum Discussion

DiddyO's avatar
DiddyO
New Member
2 years ago
Solved

Filtering a table based on other table values

Hi,

not sure whether this is somehow possible at all...

I have a table[assets] with some servernames [names] as strings.  And then i have another table that has event informations (like high cpu load on server xyz etc).. this table contains the server names just as part of short_description field...

And now i would like to build a report where i can see which server has which amount of events.. i would have to use the assets table as a kind of filter i guess? and then something with "containsstring" or so.. but i'm lost in the dark

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi DiddyO ,

    If the abbreviation of the server name in your another table that has event informations is somewhat regular, e.g., it's all the first two characters of the full spelling of name, then you can try the following:


    I use this DAX to create a measure:

    Measure = 
IF(
    ISFILTERED(assets[names]),
    IF(
        SELECTEDVALUE(event[names]) = LEFT(SELECTEDVALUE(assets[names]), 2),
        1,
        0
    ),
    1
)

    The final output is below:


    But this is only an example, your specific situation will have to ask you to provide sample data, then I can provide a more accurate solution, thank you!

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DiddyO ,

    If the abbreviation of the server name in your another table that has event informations is somewhat regular, e.g., it's all the first two characters of the full spelling of name, then you can try the following:


    I use this DAX to create a measure:

    Measure = 
IF(
    ISFILTERED(assets[names]),
    IF(
        SELECTEDVALUE(event[names]) = LEFT(SELECTEDVALUE(assets[names]), 2),
        1,
        0
    ),
    1
)

    The final output is below:


    But this is only an example, your specific situation will have to ask you to provide sample data, then I can provide a more accurate solution, thank you!

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

  • DiddyO's avatar
    DiddyO
    New Member
    2 years ago

    Hi & thanks for looking into this. It will not work like that, i need to be a bit more precise.

    Table with server-names is rather simple 

    Server Name
    abcd001-lx1210
    abcd001-lx1311
    dcef002-lx1420

    then there is the event table which contains the short description that includes the server name, but left(3) or so will not work - as it looks like this:

    Event/Short Desc.,
    (1) too high memory utilzation abcd001-lx1210
    (3) dcef002-lx1420 not enough space on folder... 
    memory_swap_used_percentage health is high on abcd001-lx1311

    hope this makes it more understable.. 

    So it would be nice if I could get a kind of count of events reported for abcd001-lx1210 etc.. 

     

    best regards

    Diddy

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi DiddyO ,

      Please try this way:

      Measure = 
VAR A = SELECTEDVALUE('server-names'[Server Name])
RETURN
IF(
    ISFILTERED('server-names'[Server Name]),
    IF(
        CONTAINSSTRING(SELECTEDVALUE(event[Event/Short Desc.,]), A),
        1,
        0
    ),
    1
)


      And use this to count the events:

      COUNT_EVENT = 
VAR A = SELECTEDVALUE('server-names'[Server Name])
RETURN
CALCULATE(
    COUNT(event[Event/Short Desc.,]),
    FILTER(
        ALL(event),
        CONTAINSSTRING('event'[Event/Short Desc.,], A)
    )
)

      The final output is below:



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

  • DiddyO's avatar
    DiddyO
    New Member
    2 years ago

    Thank you for this, had to do some minor adjustents to make it work for me - but it helped a lot.