Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi,
I know this is a bit vague but unfortunately I cannot post screenshots here for confidenciality reasons.
I have two tables:
table.Payments:
UserID | Amount Paid | Date
table.Users:
UserID | Name etc
table.Users shows all users, table.Payments only shows those that made payments. How can I filter out users who haven't made any payments in the past X months - I am looking to access live db, so I would prefer to avoid creating tables or altering db in anyway.
Thank you in advance!
Hi @lyasolis,
Could you please mark the proper answer if it's convenient for you? That will be a help to others.
Best Regards!
Dale
Hi @lyasolis
You have gotten many good ideas. I am not sure which one suits for your scenario. So there is a solution you could try.
Since you'd like to use live connection, we can't create a new table. We can create a measure here and create a table visual with columns "Users.UserID" and the new measure. (The Users[UserID] should be unique.)
Measure = IF ( MIN ( Users[UserID] ) IN VALUES ( Payments[UserID] ), "Have Payments", "Not In" )
Best Regards!
Dale
@lyasolis are you not wanting to use all the customer data at all? so you only want the users / customers who have made payments?
when you say live data, do you mean you will be pulling live data (as in live connection) or alternatively imported mode from a live database?
if its the latter, i would say merge the tables in power query and that only has a inner join kind so it will only bring back the rows that are matching and therefore filter out the unnecessary rows.
Proud to be a Super User!
Hi @vanessafvg ,
Thank you for your reply,
I want to segment users who haven't made payments, say, in April and May.
At the moment my work canvas has 2 reports:
Matrix table that gives all userID from table.Users and month by month spend per each user - so this part works fine, I can see the users who have made payments and who didn't - because there is a joint on the two tables as you suggested.
This is the issue:
Stacked Chart, that shows total revenue month by month with Currencies in Legend field (Axis: Date, UserId, Currency; Legend: Currency; Value: Amount)
So when I click on (for example) April and the USD part of the bar - the matrix report then updates and only shows me users that made payments in USD in April and so on.
But not only it doesn't explicitly show users that didn't make payments, I have no way of filtering them out anywhere as a list...
I thought since I have UserId in the Axis, it should show the ones without payments in negative on the bar chart?
Step Two - I would like those users
In regards to live data - I am new to this so correct me if I am wrong: I want the data to be updated on refresh, I will be using it for dashboard, so it's not a once off report I am looking for.
Hi, let me try one thing (If i understand your question)
When you select a bar in your chart, you want that matrix show users with payments and also with no payments in the month selected.
If the answer is YES please follow the steps in the image.
@lyasolis please can you put screenshots of what you are saying /wanting/ feel free to blurr or scratch out any data that is personal if thte below doesn't help.
in your visual, under your visual level filters (right side of canvas below where you place your values, can't you filter them out there on your measure?
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |