Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
A practical question:
I have a number of customer in my database and I am showing the number of transactions they had over time. However, my customers can have different statuses, e.g. 'paying', 'trial' and each status is recorded with a start and end date.
Some example records in this MerchantStatus table would be:
CustomerId StatusName StartDate EndDate
1 Trial 01-12-2016 31-12-2016
1 Paying 01-01-2017 01-05-2017
2 Paying 01-03-2017 01-06-2017
My problem is now that I want to show the transactions of my customers over time, with a filter such that only 'Paying' customers are included in the transaction-over-time graph, and I'm not quite sure how to do this. My goal is that the graph only shows transaction data from customer1 from 01-01-2017 until 01-05-2017 and not earlier, etc.
Can someone please help me out how to create such a filtered graph? Will I have to re-arrange my data model?
Any ideas or pointers are welcome!
Hi, you can write a DAX function which will filter the column on Paying. Try replicating a below DAX.
PayingColumn = CALCULATE(COUNTROWS(TableName),FILTER(TableName,TableName[StatusName] = "Paying"))
I have assumed that you will be using count of rows for your line chart hence CONTROWS.
Take this new column and put into your line chart as a Value. This should work for you. Do reply back with your results,
Thanks
Yash
Hi @Anonymous,
Thanks a lot for helping out! I feel like your answer is getting a bit closer, but it is still not quite what I need, unfortunately it's a bit more complex, and this is why:
My CusteromActivity table consists of aggregated numbers per merchant, per date. For example:
CustomerId Date Transactions ....
1 01-01-2017 10
2 01-01-2017 53
1 01-02-2017 12
etc.
So in my graph I want to show all transactions per date, with a filter such that only transactions from merchants are included if they had a status 'paying' on that Date. So in short I want to prefilter my CustomerActivity table based on the Start and EndDate in the CustomerStatus table, with status = 'Paying'.
Maybe you also know how to do this?
Hi @Fraukje,,
If you want to put a prefilter than try to add a new calculated column with an IF DAX and then use that column for futher analysis in Dashboard panel(?), something like this may help you.
=PayingStartDate = If(TableName(Column) = "Paying",DateValue(TableName(StartDate)))
another column for End Date
=PayingStartDate = If(TableName(Column) = "Paying",DateValue(TableName(EndDate)))
Try this out and see if it works.
All the best. Thanks
Hi,
have u tried Timeline Storyteller visual from the store?
Hi,
Thank you for your answer! I did not look into this yet, but my preference would be to have a line chart with some kind of filter on it, if possible of course.
@Fraukje,
I make a test using the following table.
I create a Line chart as follows, set the value of StartDate to "after 1/1/2017 and before 1/5/2017" in visual level filter, then use the StatusName slicer to filter only paying transactions. If you have any questions, please feel free to ask, and we will appreciate that if you can post expected result.
Regards,
Lydia
Hi @v-yuezhe-msft,
Thanks so much for your answer. In your case it is quite straight-forward to use the filtering. However, my data is organized differently. In my case, the data is more normalized, such that I have:
I feel like in this case the filtering is not possible, and the easiest way to obtain the result I want is to create a view in my database first, in which the customer activity table is filtered by the status table start and end date. If you would know a way in PowerBI without this work-around, I would be really happy to find out how!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |