Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AkrMus
Helper I
Helper I

Linked tables with slicer

I have 1 table 

that has these data

 

IDCodeValueGroup
1KJ13571Finance,HR
2KJ82415Operation,Finance
3TF72454HR,Operation,Finance
4KJ58362Operation
5ND77607HR,Finance
6KJ83350Operation,HR
7TF96531Finance
8YT39840HR,Finance
9SS34976Finance,HR
10ND26155Operation,Finance

 

I am filtering by Group through slicer

 

due to business requirements , slicer has to be in a seperate page

 

to have slicer for groups

I created a copy of the original table and split it by comma

 

both the copy and the original one are linked by the ID

 

 

my problem is that when I filter by group the origonal table does not filter

 

I have attached pbix file to make it clearer

https://we.tl/t-FaRljwOeYv

 

 

I appreciate if some one can tell me how to make original table filter based on the slicer

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Can you make the relation bi-directional and try. As the new table is a child(1-many) it will not filter the original table.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

I have a related question about using slicer to filter data in tables with no established relationship. Say I have a list of dates, EOM (End of Month). It goes like this: EOM 1/31/2000 2/28/2000 ... 1/31/2020 I put this in a slicer. Somehow I still get a continuous date displayed as I slide the slicer left or right. However, if I display the end data points of the slicer I do get an EOM such as 9/30/2019 (but the last date could be 10/19/2019 displayed as the right-most date in the slicer). Here's the question. If I have a table with Period (YYYYQ) how do I filter this using a slicer? YYYY is Year & Q is Quarter. This table looks something like this: Ticker, Period, Value Ticker1, 20193, 3.45 Ticker2, 20193, 50.20 ... TickerN, 20193, 17.50 Ticker1, 20194, 4.51 Ticker3, 20194, 62.30 ... TickerZ, 20194, 100.23 ... If I don't have Period, but Date. Then I could use a measure such as this: SelectedDate = Calculate(max(EOM[EOM]), AllSelected(EOM[EOM])) Say, I wanted to sum up all the value based on the selected ending EOM. Say, it's 9/30/2019, not 1/31/2020. My other measure would be this: ValueSelected = Calculate(sum(table[Value]), Filter(table, table[Date]=table[SelectedDate])) That is, if I have Date rather than Period. How would I do this if I have Period as above? Frankly, I don't even know why SelectedDate = Calculate(max(EOM[EOM]), AllSelected(EOM[EOM])) give the correct result when SelectedQtr = Calculate(max(EOM[EOM].[Quarter]), AllSelected(EOM[EOM])) gives 4 & SelectedYear = Calculate(max(EOM[EOM].[Year]), AllSelected(EOM[EOM])) give 2020 It seems the last 2 max assess the entire EOM table rather than the selected band. I think SelectedQtr give 4 because other years has 1,2,3,4 & 4 is the max. There's no way I could structure the quarter to be the latest selected quarter from the latest year.
amitchandak
Super User
Super User

Can you make the relation bi-directional and try. As the new table is a child(1-many) it will not filter the original table.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Found it

 

Works 

 

Many thanks

how to make it bi-directional?

Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, when splitting data by comma, click on Advanced options and select Rows.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I did

 

This is how it is at the moment

The original Table will not be filtered when you select a Group in the slicer.  For all practical purposes, the original Table now becomes the one in which the Group column has one value per cell.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.