How to show certain values in the rows of a table based on selections in the slicers

[alexpegg86]

I'm trying to create a data table in my pbi report where a user can select a retailer, and the rows of the table are filtered to all the retailers in the environement for that selected retailer.

Here is the data for the retailers and their envionments:

Retailer	Environment
Aldi	Euro Discounters
Lidl	Euro Discounters
Asda	Grocery
Iceland	Grocery
Morrisons	Grocery
Ocado	Grocery
Other Mult Grocers	Grocery
Sainsbury	Grocery
Tesco	Grocery
Total Co-Operative Group Kad	Grocery
Waitrose	Grocery
Convenience Multiples	Total Impulse
Independents	Total Impulse
Multiple Forecourts	Total Impulse
Symbols	Total Impulse

So for exmaple if i selected Aldi in the retailer slicer, i would want the rows in the table to just show Aldi and Lidl. 

If i selected Asda, i would want the rows to show all those retailers in the Grocery environment (Asda, Iceland, Morrisons etc).

Any help on this would be much appreciated!

Thanks.

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached file.  Please review it and adapt the solution to your dataset.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached file.  Please review it and adapt the solution to your dataset.

Hope this helps.

[Anonymous]

Hi @alexpegg86 ,

For your question, I think you can use the New group feature.

where you can categorize your needs.

This way you can add the new group to slicer and get the results you need.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alexpegg86]

Hi,

Thanks for taking the time to respond. 

I'm not sure if the grouping function can give me what i'm trying to see. Below is a screenshot of my report.

So when i select "Asda" in the filter, as expected it will filter the table to Asda only... what i would like it to show is Asda as well as all the other customers in the same environment as Asda, like in the screenshot below:

I think @Ashish_Mathur with your solution i can get close, but not quite there. I'll kepe playing around with it so see if i can find the solution.

[Anonymous · Accepted solution]

Hi @alexpegg86 ,

Previously I used new group, which works but seems cumbersome. So I used another method where I copied an identical table. Created a two Measures.

Measure =
VAR _A =
    SELECTEDVALUE ( 'Table 2'[Retailer] )
RETURN
    CALCULATE ( SELECTEDVALUE ( 'Table 2'[Environment] ), 'Table 2'[Retailer] = _A )

Measure 2 =
IF(SELECTEDVALUE('Table'[Environment])=[Measure],[Measure],BLANK())

Then I get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alexpegg86]

Thanks @v-yilong-msft   - this has nearly got me there!

I was wondering if you could take a look at the attached pbi file. I've now added numeric data into the table, and i'm trying to get table 3 to filter as it would without the numeric data in table 1.

So in the attached there are three tables:

1. Table without numeric data
2. Table with numeric data direct from the Volume column of data
3. Table with numeric data from a measure

It's table 3 that i wanted to know if the data will filter to how it is in the other two tables, as in my actual pbi report i would need to use a measure to get the data i need.

Do you know if this would be possible?

[alexpegg86]

Just realised i can't upload a pbi file to show you 😐

[Greg_Deckler]

@alexpegg86 Use a Complex Selector: The Complex Selector - Microsoft Fabric Community

[alexpegg86]

Thanks for sharing Greg - i had a look at that post but can't figure out how i'd apply it to my data, as your example looks at summing data whereas i just want to simply show certain values based on what is selected in a slicer.

Any idea how i could go about this?

Alex