Forum Discussion

aris's avatar
aris
Frequent Visitor
4 years ago
Solved

Filter per category

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/NAC type
A737
A787
B737
C 
D787

 

 

P/NQty
A10
B1
C2
D54

 

Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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.

11 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    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's avatar
      aris
      Frequent Visitor
      4 years ago

      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's avatar
        bcdobbs
        Community Champion
        4 years ago

        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() )