Display all alternatives to the selected value

[trdoan]

Hi everyone,

I have the following tables:

1. "NDT" table

Store	NDT Capability
A	STI
A	FPI
B	Radio
B	FPI
D	FPI
D	STI
E	Radio
T	STI
T	FPI
T	Radio
H	Radio

2. "Welding" table

Store	Welding Capability
H	TIG
H	Laser
H	Electron Beam
B	TIG
B	Laser
T	Electron Beam
T	Laser
T	TIG
O	Laser
C	Laser
C	TIG
J	TIG

3. "DER" table

Store	DER Repair	Specify DER Repair
A	YES	Explantion
B	NO
C	NO
D	NO
E	NO
F	NO
H	NO
J	NO
K	NO
L	YES	Explantion
O	YES	Explantion
T	NO

4. 'General Info' table (Store column has all Store names, unlike the above 3 tables that might or might not have a full list of stores)

StoreName	Address
A	USA
B	UK
C	Canada
D	Japan
E	USA
F	UK
H	USA
J	USA
K	Singapore
L	France
O	France
T	France

Here are my questions:

Question 1. Assuming Store A is selected, is there a way to count and then show all its capabilities?

Ex: In the above data, A is capable of 2 things:

• NDT Capability: STI, FPI
• DER Repair: Yes

Expected result:

Count of capabilities = 2

Details = NDT Capability (STI, FPI)

               DER Repair (Yes)

Next, assuming there are 4 slicers created from the following columns: 

• Store Name  - 'General Info'[StoreName]
• DER Repair - 'DER'[DER Repair]
• Welding - 'Welding'[Welding Capability]
• NDT - 'NDT'[NDT Capability]

Question 2. If Store H ( from "Store Name" slicer)  &  Electron Beam and/or TIF and/or Laser (from "Welding" slicer) are selected, is there a way to filter down to ONLY alternative stores that are able to do that/those thing(s)? (SINGLE/MULTIPLE SELECTIONS ON 1 SLICER)

Expected Result:

• With 'H' & 'Laser' selected, alternatives to H = H, B, T, O, C   

         Count of Alternatives = 5

• With  'H' & 'Electron Beam' & 'Laser' selected, alternatives to H = T

         Count of Alternatives = 1

Question 3. If Store H ( from "Store Name" slicer) & Electron Beam (from "Welding" slicer), & Radio (from "NDT" slicer) are selected, is there a way to filter down to ONLY alternative stores that are able to do both those Welding & NDT? (SINGLE/MULTIPLE SELECTIONS ON MULTIPLE SLICERS)

Expected Result:

• With 'H' & 'Electron Beam' & 'Radio' selected, alternatives to H = T

Please can you show me how to do this? Thank you so so much!

[v-cherch-msft]

Hi @trdoan 

You may add a slicer table as slicer to display alternatives. Check the relationship and three pages in attachment for reference.

Slicer_StoreName = DISTINCT('General Info'[StoreName])

Regards,

Cherie

[trdoan]

Hi @v-cherch-msft , thanks for your suggestion.

Question 1:

Your file seems to work perfectly the way I want it to be, however, when I did it on my file, nothing worked although everything in my file was pretty much identical to yours. None of the measures you wrote for me worked on my sample file or on my real data.

My file:

Your file:

Here is the link to my file.  At work, I made this file from Power BI version September 2018 (and obviously my Power BI at work is unable to open the file you posted). Therefore I had to open your file on my personal PC which has the latest version and everything worked well. 

I don't know if the version is the reason? Do you have any idea why and how to tackle this issue? Thank you so much!

Question 2: Do you know if it is possible to count distinct the number of capabilities each store has using only 1 measure (that can be later put in a card visual to show the number)?

For example, if H is chosen:

• the number of capabilities it is capable of = 2
• list of capabilities = Welding ; NDT

Can you please advise? Thank you so much!