cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mdiz Frequent Visitor
Frequent Visitor

SSAS live connection: 2 step filter

Hi i'm connecting to our DW through an SSAS live connection. Star schema roughly as follows:

 

dimCustomer - list of customers

dimEngagementType - list of possible actions a customer can take to engage with us (e.g. registered for webinar)

factCustomerEngagement - transaction fact of all customer engagements

 

I've been given the following requirement: show me all customers who performed a particular action (e.g. registered for webinar) and then show me all other actions they took and when

 

So essentially i need to filter the fact table twice. Once on engagement type to get the list of customers who performed the particular action. Then i need to re-filter it using that customer list to show me what else they did.

 

Can i do this in DAX? I come from a SQL background so trying to rewire my brain.

Thanks for any help.

 

 

5 REPLIES 5
Stachu Super Contributor
Super Contributor

Re: SSAS live connection: 2 step filter

you can query SSAS using DAX so it would return a table - which would be very SQL thing to do, and I don't recommend it Smiley Happy


what would more DAX focused approach is following:
1) create a measure that shows nr of engagements, e.g.

NrOfEnagements = COUNTROWS(factCustomerEngagement) -- assuming that each row is single engagement

2) crate a visual using Customer Name from dimCustomer and [NrOfEnagements ] - it will only show customers that have engaged, and the number of such actions
2) if you then use slicer to filter e.g. 'registered for webinar' in dimEngagementType it will pass the filter from the slicer to the visual, effectively showing all the customers that have made that action and the number of such actions

 

you could also write it explicitely like this:

NrOfRegistrations = 
CALCULATE( 
    COUNTROWS(factCustomerEngagement), 
    dimEngagementType[Action] = "registered for webinar"
)

or referencing the first measure

NrOfRegistrations = 
CALCULATE( 
    [NrOfEngagements], 
    dimEngagementType[Action] = "registered for webinar"
)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mdiz Frequent Visitor
Frequent Visitor

Re: SSAS live connection: 2 step filter

Thanks for the response, really appreciate it. However won't those formulas just give me a count by engagement type? What i need to answer is:

 

Of the 5 people who registered for a webinar, what other actions did those 5 people do? How else did they engage with us?

 

Does that make sense?

 

 

Stachu Super Contributor
Super Contributor

Re: SSAS live connection: 2 step filter

the measure always gives different values dependng on filter context coming from visuals, report/page/visual filters etc. and how visual is structured
for your example you can use [NrOfEngagements] together with Customer Name in the visual and only use [NrOfRegistrations]<>0 in the filter of that visual

I'd suggest just trying these measures in different layouts, once you have more clear idea how you want to present the data and what to calculate we can adjust the measures
e.g. you can create measure using CONCATENATEX that would return a string of all actions for the users that have nrofregistrations>0, which is possible but is quite unusal way of presenting data
right now I'm not clear what you want to present and in which format

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Stachu Super Contributor
Super Contributor

Re: SSAS live connection: 2 step filter

or in case the actions is supposed to be dynamic it is even more dependant on the visual setup:

1) how do users pick up the action to be referenced (slicer?) Can there be multiple actions selected or only one at a time? if there are more should the which operator should be used (AND/OR?)
2) do you want to show nuber of categorised number of other actions, or action names? if action names how do you want to aggregate multiple actions for a single customer?

etc.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
mdiz Frequent Visitor
Frequent Visitor

Re: SSAS live connection: 2 step filter

Sorry for the delay getting back to you on this. Appreciate the followup response. Think i follow.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 97 members 1,545 guests
Please welcome our newest community members: