PowerBI report slicers that navigate 1-to-many relationships

[quijote]

Sorry, I am new to PowerBI and I have an issue regarding slicers on reports which are based on tables that have 1-to-many relationships between them.

Context

(i) I have one table called 'TICKET' with columns that include [TicketId], [Date] and [RaisedBy].

(ii) Another table called 'TICKET_DETAILS' that is related to 'TICKET' via a 1:Many relationship ('TABLE' is on the 1 side)

(iii) The 'TICKET_DETAILS' table has columns that include [TicketId] and [CountriesImpacted].

i.e. a Ticket can impact multiple countries.

My PowerBI report currently shows the COUNT OF [TicketId] by the [Date] of each Ticket, with a slicer on 'TICKET'[RaisedBy] to filter the date by who raised the ticket.

So far, so good.

My Question

Q. Can I create a slicer that further filters tickets by 'TICKET_DETAILS'[CountriesImpacted] (thus parsing the 1:M relationship)?

The DAX function:

RELATED()

  only navigates M:1 relationships (not 1:M).

At the moment, I am having to use DAX to create a new calculated column on 'TICKET', called [CountriesImpacted] that contains a comma-separated list of the 'TICKET_DETAILS'[CountriesImpacted] values.

However, the slicer gives me a duplicated list of all countries impacted, causing a poor user experience.

Any ideas on how to add a slicer that navigates 1:M model relationships to filter a report?

Any help much appreciated, thanks.

[quijote]

Thank you so muc - that is really helpful !

[DataInsights · Accepted solution]

@quijote,

One approach is to merge the TICKET table into TICKET_DETAILS. The columns in TICKET would be moved to TICKET_DETAILS, leaving you with one fact table. Then create a star schema by creating dimension tables such as Country and Date. The Country dimension would have a 1:M relationship with TICKET_DETAILS, enabling you to filter on CountriesImpacted. If TICKET has metrics such as TicketPrice, you'll need to handle them properly in TICKET_DETAILS to avoid duplication (e.g., you could allocate TicketPrice across each ticket's rows in TICKET_DETAILS).

https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/