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/...
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.

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

       

      • bcdobbs's avatar
        bcdobbs
        Community Champion
        4 years ago

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