Filter customers on time-dependent status

[Fraukje]

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!

[Anonymous]

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

[Fraukje]

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?

[Anonymous]

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

[Kisi3ll]

Hi,

have u tried Timeline Storyteller visual from the store?

[Fraukje]

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.

[v-yuezhe-msft]

@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

[Fraukje]

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:

• A customer table (with CustomerId, Name, etc)
• A customer activity table (with number of transactions per date, per customer)
• A customer status table (status, with start and end date)

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!