Checking if element belongs in same groups as a selected one.

[biofaust]

I have a dashboard showing different data about different events.
The selection of a specific event to investigate is done on a slicer showing a "short description" for each event.

In a table CURRENT_EVENTS I have an [Event Name] column which contains a list of unique event names and the associated Short description:

Event 1 | Description 1
Event 2 | Description 2
Event 3 | Description 3
...

This is also the table that contains relates to another table containing trends about each event.

In the model there is also an EVENT_GROUPS table which is as follows:

Event Name	Group Num
Event 1	1
Event 2	1
Event 3	2
Event 4	2
Event 5	1
Event 5	2
...	...

The two tables are connected on Event Name = Event Name with a One-to-Many relationship, Cross-filter direction is Single and Relationship is Active.

I want to obtain that, if I select the short description for "Event 5" in a slicer, I get a Table visual to show the data related to:
Event 1
Event 2
Event 3
Event 4
Event 5

But if I select the short description for "Event 1", I get the Table visual to show the data related to:
Event 1
Event 2
Event 5

Since they all share common GROUP_NUM values.

Any pointers?

[ryan_mayu]

@biofaust 

pls see if this is what you want

Do not create relathionship between two tables.

create measures

Measure =

 VAR _group=distinct('Table'[Group Num])

 var tbl=FILTER(all('Table'),'Table'[Group Num] in _group)

 return CONCATENATEX(tbl, [Event Name],",")

Measure =

 VAR _group=distinct('Table'[Group Num])

 var tbl=FILTER(all('Table'),'Table'[Group Num] in _group)

 return CONCATENATEX(tbl, [Event Name],",")

and add the measure 2 to visual filter and set to 1

pls see the attachment below

[biofaust]

It is not working for me.
The difference being that I need to have the slicer set to use a "description" field in your "Table (2)".
When I change your file in that respect I get the same behavior, with the Table visual returning only the selected value.

[ryan_mayu]

then could you pls update your sample data and elaborate more about the expected output?

[biofaust]

I edited the post, making it very general but explaining better the problem I have.
Thanks for bearing with me!

[ryan_mayu]

so we have two tables.

one contains event name and description. event name is unique

Event 1 | Description 1
Event 2 | Description 2
Event 3 | Description 3

the other table contains event name and goupnum

Event Name	Group Num
Event 1	1
Event 2	1
Event 3	2
Event 4	2
Event 5	1
Event 5	2

when we select description, you want to display event name.

The name is from which table?

[biofaust]

The slicer I already have works on the first table.

The table visual I have already does not feature the event name, but instead shows a list of the associated descriptions as the row headers.
All the other data about the events comes from a third table towards which the first one has another one-to-many relationship.

I have managed to obtain the right list of Related Events using this function:

RelatedEventsList = 
VAR SelectedEvent = SELECTEDVALUE(CURRENT_EVENTS_2[Event Name])
VAR SelectedGroups = 
    CALCULATETABLE(
        VALUES(EVENT_GROUPS[GROUP_NUM]),
        CURRENT_EVENTS[Event Name] = SelectedEvent
    )
VAR RelatedEvents = 
    FILTER(ALL(EVENT_GROUPS), EVENT_GROUPS[GROUP_NUM] in SelectedGroups)
RETURN
CONCATENATEX(
    RelatedEvents,
    EVENT_GROUPS[EVENT_NAME],
    ", "
)

In which CURRENT_EVENTS_2 is a disconnected table created using:

CURRENT_EVENTS_2 = 
SUMMARIZE(
    CURRENT_EVENTS,
    CURRENT_EVENTS[Strike Name],
    CURRENT_EVENTS[Short Description],
    CURRENT_EVENTS[Activation]
)

, but there are 3 problems:

1. The Events with a double entry in EVENT_GROUPS show up twice in the list.
2. I have no idea how to use this for a Measure that would check if the other events belong to the same group(s).
3. It uses another slicer based on CURRENT_EVENTS_2 to select the event, which is something I cannot have on the dashboard.

[DataNinja777]

Hi @biofaust ,

I would avoid using many-to-many relationship as it introduces ambiguities to your data model, but the following dax formula might work:

IsInSameGroup = 
VAR SelectedEvent = SELECTEDVALUE(CURRENT_EVENTS[Event Name])
VAR SelectedGroups = 
    CALCULATETABLE(
        VALUES(EVENT_GROUPS[GROUP_NUM]),
        EVENT_GROUPS[Event Name] = SelectedEvent
    )

VAR CurrentEventGroups = 
    CALCULATETABLE(
        VALUES(EVENT_GROUPS[GROUP_NUM]),
        EVENT_GROUPS[Event Name] = MAX(CURRENT_EVENTS[Event Name])
    )

RETURN
IF(
    COUNTROWS(
        INTERSECT(
            SelectedGroups,
            CurrentEventGroups
        )
    ) > 0,
    TRUE(),
    FALSE()
)

Best regards,

[biofaust]

Sadly not.
What I notice, in the visuals I set up for debugging, apart from the SelectedEvent and SelectedGroups being right, is that the value of the new measure is True even if no Event Name is selected for all the Event Names that have a row in the second Table, while all the others are False.
These values do not change when I select an event.

For clarification, not all events have a row in the second table.

Also, I may ask for the data to be organized in a different way to avoid Many-to-Many relationships.
Please advice on what would be the optimal way to have the data organized for Power BI.