Filter per category

[aris]

Hi !

I have 2 tables, Table A shows part number applicability , Table B shows stock levels.

I want to create a report that shows P/N and q-ty per selected AC type. I tried (snip below) but i get P/N A listed twice (and double q-ty) because it is linked to 2 AC types.

Any help appreciated! 🙂

P/N	AC type
A	737
A	787
B	737
C
D	787

P/N	Qty
A	10
B	1
C	2
D	54

[bcdobbs]

Can you mock up the output you actually want in excel?

I think the way to go is using CONCATENATEX but want to be sure of the output you need.

[aris]

hi bcdobbs 

i will create button shape filters for each AC type:

Depending on selected button(s), here is what i expect to see:

for single selections its not a problem, its multiple AC types selected that give wrong total q-ties.

thanks 🙂

[bcdobbs]

Have a go with this demo: Applicability Demo 

Rather than dropping the A/C column into the visual use this measure instead (and use P/N from your stock table):

Applicable Types = 
VAR Result =
   CONCATENATEX(
        VALUES(Applicability[AC type]),
        Applicability[AC type],
        ", ",
        Applicability[AC type], ASC
    )

RETURN IF ( ISINSCOPE(Stock[P/N]), Result, BLANK() )

[bcdobbs]

Actually I think I'm doing it the opposite way round to what you want! Will update.

[aris]

that looks very promising!! not sure if it would be possible for ex. that only selected AC types are shown in the table..  i selected 787 and total q-ty is 67. if non relevant where hidden then it would be perfect 

[bcdobbs]

I think my change to the quanity measure mentioned below does what you need.

Hoping latest version has now synced up to cloud! Give it a minute and download a fresh copy from  Applicability Demo 

[bcdobbs · Accepted solution]

Have updated the demo file. The quanity measure needed to read the p/n currently needed...

Quantity = 
CALCULATE(
    SUM ( Stock[Qty] ),
    TREATAS( VALUES(Applicability[P/N]), Stock[P/N] )
)

Could have made relationship bidirectional but this is safer.

[aris]

PERFECT!!! thank you so much bcdobbs! you are genious 🙂 

[bcdobbs]

Enjoyed it! Looked simple to start with but there are some subtle requirements! Thanks.

[bcdobbs]

Pondering on your data model.

I think it's a legitimate use case of a bidirectional relationship. Especially if you only have those two tables. Would remove the need for the TREATAS. 

[aris]

hi bcdobbs

yes, birectional is the only available i can chose..

i havent used the Quantity measure and it adds up correctly.

The next challenge, how could i filter if i want by A/C type that is unique : ex. 787 only.

so in my initial example, by chosing 787 it would normally show only D; A would be excluded as its both 787 and 737.

I think the easiest to solve this is by using filter "does not include" in applicable types  ? 😊

Rgds

Aris