Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

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 CUR...
Need Help
DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
2 years ago

Hi Anonymous ,

 

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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.