Filtering a table based on other table values

[DiddyO]

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

[DiddyO]

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

[DiddyO]

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

[v-junyant-msft]

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.

[v-junyant-msft · Accepted solution]

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.