Dynamically using visual/query output as input

[Fraukje]

In a report for a specific customer, I show the top-3 countries where his fraud is originating from. The table I use for this is called CustomerFraudCountry and constists of daily counts of how many times a country was included in a fraud cases, per country, per customer, per date.

Example data:

Id    Date              CustomerId              Country             Occurence
1     01-01-2017  1                              United States     10

2     01-01-2017  1                              Canada              7

3     01-01-2017  2                              United States     8

etc...

A report level filter is used to filter on CustomerId and a certain Date range.

So let's say for my CustomerId = 1, and a certain Date range 01-01-2017 until 31-05-2017 selected, I have a top-3 countries of

1. United States

2. Canada

3. Mexico,

visualized in some bar chart.

What I would love to do know, but I am totally stuck trying to do, is to show a pie chart that shows the top-10 customers (excluding CustomerId = 1) that have the most fraud cases uploaded with these top-3 countries (United States, Canada, Mexico).

There are currently two problems I cannot seem to find a solution for:

1. How to circumvent my report filter

2. How to use the outcome of some visual (/query) as input for another visual. I.e. rank my table using the dynamically determined countries 'United States', 'Canada', and 'Mexico'.

I would be very pleased to hear any ideas or suggestions. Even if you think it is impossible to do, please share any ideas of an workaround, while still allowing for dynamic changing of data, i.e. Date and CustomerId.

 Note: I use DirectQuery

[v-yuezhe-msft]

@Fraukje,

For your first question, you can put all the bar charts in a single report page, then use page level filters instead of report level filter to filter on CutsomerId and Date range. This way, you can put pie chart in another report page and use visual level filter to filter on specific fields.

For your second question, you may need to create new measure or new table, please share complete data of your table and post expected result here.

Regards,
Lydia

[Fraukje]

Hi @v-yuezhe-msft (Lydia),

Thanks for thinking with me here. Putting all the charts in one page and using the page filter is not an option for me, since there are 15 pages filled with charts and is used to export to PowerPoint and present at the customer.

Currently I have created the following workaround which I think is not quite workable, since it requires a lot of manual work (and might be too tricky for the users of this report).

What I have done is:

- Import the same table again, and remove the relation to Customer (such that the report filter no longer applies).

- Import the Customer table again, and set up a relation to this Customer(2) table.

- Set up visual filter for Customer.Name (used to filter out the Customer of the report filter)

- Set up visual filter for Country.

Unfortunately both the Customer.Name filter and the Country filter will have to manually set each time. My question is now, is there a smarter way to do this?

1. I want the Customer.Name to be the same as the Customer.Name that is dynamically chosen in the report filter.

2. I want the Country to be the same as the top-3 countries from my bar chart that is dynamically dependent on Date and Customer chosen in the report filter.

[v-yuezhe-msft]

@Fraukje,

Another method is to create a measure/column using All() function to calculate the top 10 customer, ALL() function ignores all slicers and Visual/Page/Report filters.

If you have questions about the dax, please share complete data of your table and post expected result here. Do mask sensitive data before uploading sample data.

Regards,
Lydia