Filtering with filtered results of a visual

[T4S3R]

Hi everyone,

I want to filter a given table / visual based on a country filter. This filter will contain a single country. Based on the filter, i get the "Bestellnummer" (basically an Order Number). 

here is where I am stuck:

I want to display all the "Bestellnummer" that has been filtered in the top visual as well as ALL Countries regardless of what is filtered in the "Land" filter.

Best regards

Timo

[amitchandak]

@T4S3R , are the part of the same table or different tables. Can you share sample data and sample output in table format?

[T4S3R]

Hi @amitchandak,

thanks for the fast reply, the data is within a single table. Adding a new table based on existing data would not be a problem though.

I created a zip file containing the sample data and desired output in an Excel file as well as a pbix file containing these sample data and further explaining the desired result.

https://1drv.ms/u/s!AsBkF7PVhL4Ekxo66NIWovGP4fW_?e=fg8Vh2

If you cannot open Files due to security, here is the sample Data I used:

Country	Salesnumber
DE	234
DE	324
DE	123
DE	312
EN	234
EN	17
EN	123
EN	324
EN	24
EN	25
EN	3

and here is the desired outcome:

- Filter is set to DE

- Table contains just the filtered Salesnumbers from the country DE in rows but all Countries and a count of Salesnumbers.

	DE	EN
234	1	1
324	1	1
123	1
312	1

Best regards and thanks fr the help as always!

Timo

[T4S3R · Accepted solution]

Okay I figured it out. @amitchandak 

1. You can create a new table that copies all country values

Table 2 = SELECTCOLUMNS('Table',"Country",'Table'[Country])

2. Create a m*n relationship between the new table and the existing one.

3. Create a Measure with the ALL argument that Counts the Salesnumber

Measure = CALCULATE(COUNTA('Table'[Salesnumber]),ALL('Table'[Country]))

4. Use the Salesnumber from the first Table, the created Measure and the Country field from the Table 2.

If you found a better / smoother solution would be highly appreciated 🙂

Best regards 

Timo