Need help in designing SSAS model and use it using power bi report for the following scenario

[Anonymous]

Hi,

I have following tables in my SSAS model  :

1. a) Event Table containing list of events and having following columns
2.        i) EventNumber -> Unique identifier
3.        ii) EventTitle

         iii) StartDate

1.        iv) EndDate
2. b) EventTeam table containing list of users and the role in which they are associated to an event
3.        i) EventNumber -> Joining key to Event Table
4.        ii) Name

         iii) Role

1. c) EventRequest table containing list of request related to an event (An event can have multiple requests/tickets)
2.       i) RequestId - Unique identifier
3.       ii) FirstName

       iii) LastName

1.      iv)  Date
2. d) BridgeEventRequest table to join event and eventrequest tables
3.        i) EventNumber -> Connector to Event Table
4.        ii) RequestId

         iii) Id

1. e) ServiceDesk table containing list of participants realted to an event request (An event request can have multiple participants)
2.        i) RequestId
3.        ii) SequenceNumber (unique id for this table)

          iii) Participant Name

1.         iv)  EmployerName

My requirement is to show a power bi report which allows user to:

1. Display list of events (from Event Table) and corresponding request count (EventRequest table) for an Event Team Name/Role slicer (page 1 of power bi)
2. On selection of an event (displayed on page 1), display details of all the requests (from EventRequest table) associated with event using Drill through filter on second page of power bi. In addition to request details, I need to display the list of participant details (from Service Desk table )also in separate table on page 2 based on event selected on page 1.

      Could you please help me to design the model in SSAS and use it in power bi?

Thank you

[v-yuta-msft]

@Anonymous ,

You can create measure/calculate column in power bi/SSAS to achieve the count value using dax function like COUNT(), ALLSELECTED().

For the second requirement, you can use drill through page, please refer to doc below:

https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

Community Support Team _ Jimmy Tao

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

[Anonymous]

@v-yuta-msft: Thank you for guiding me towards solution

Current status

      I have created a SSAS cube as following:

            a) EventTeam table has many to one relationship with Event table with cross filter direction "Both"

                    - EventNumber is the connecting column

            b) BridgeEventRequest table has many to one relationship with Event table with cross filter direction "Single"

                    - EventNumber is the connecting column

            c) BridgeEventRequest table has many to one relationship with EventRequest table with cross filter direction "Single" 

                     - RequestId is the connecting column

      I have created a Power BI report with drill through filter:

             Page 1 of report contains following

                   a) Slicer based on Name/Role column from EventTeam table (to filter events) -> it works

                   b) Displays Events details(from Event table) showing Event Number, Event title with associated request count (from                                  Bridge table). Request Count is used as drill through filter -> it works

            Page 2 of report contains following 

                    a) Request details (from EventRequest table), it gets filtered using drill through filter passed from page 1 -> it works

                    b) Participant details (from ServiceDesk table), it also needs to get filtered based on drill through filter passed from   

                                      page 1 --> not working

     Need help with following:-

          a) Establish relationship between ServiceDesk table with Event/EventRequest table in SSAS cube 

          b) Filtering Participant details (from ServiceDesk table) in page 2 of Power BI report based on drill through filter value

Thank you

[v-yuta-msft · Accepted solution]

@Anonymous ,

If you are using live connection, you can't modify the report model on power bi side(e.g.: build relationship). You can only create relationship in AS, please refer to:

https://docs.microsoft.com/en-us/analysis-services/tabular-models/create-a-relationship-between-two-tables-ssas-tabular?view=sql-analysis-services-2019

However, if you are using import , you can do that in power bi, https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

About the how to pass filters using drill through, you can read doc below:

https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough#add-a-measure-to-drillthrough

Community Support Team _ Jimmy Tao

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