Forum Discussion

Milan's avatar
Milan
Frequent Visitor
5 years ago
Solved

Find data in filtered table

Hello,

 

I am trying to find results from different table, but all attempts didn´t work.

 

I have a table A (Store cards - no duplicates):

1. Column - Store card name

2. Column - Custom column, where i would like to show result Amount from Table B. Filtered by specific values.

Number of warehouse = 1, Date of status = 30.6.2021. That´s mean one result (amount) for one card.

 

Table B (Warehouse status):

1. Column - Store card name

2. Column - Number of warehouse

3. Column - Amount

4. Columnt - Date of status

 

Thanks for your time and help

 

Milan

 

I made easy table for better understanding.

I tried 

Custom column = LOOKUPVALUE('Table B'[Amount],'Table'[Storecard name],'Table A'[Storecard name])
This is working just for one date and one warehouse. I don´t know, how to use conditions in formula.
 

 

 

Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi Milan ,

     

    If you want to create a calculated column, you can try the expression below. But it is static and can't changed dynamically based on slicers.

    Column = 
CALCULATE (
    SUM ( 'Table B'[Amount] ),
    FILTER (
        'Table B',
        'Table B'[Storecard name] = 'Table A'[Storecard name]
            && 'Table B'[Warehouse] = 1
            && 'Table B'[Date] = DATE ( 2021, 6, 30 )
    )
)

     

    It is suggested to create relationships between Table A and Table B, and then you can use slicers to change the value dynamically like so:

     

    For more details, please check the attached .pbix file.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Milan ,

     

    If you want to create a calculated column, you can try the expression below. But it is static and can't changed dynamically based on slicers.

    Column = 
CALCULATE (
    SUM ( 'Table B'[Amount] ),
    FILTER (
        'Table B',
        'Table B'[Storecard name] = 'Table A'[Storecard name]
            && 'Table B'[Warehouse] = 1
            && 'Table B'[Date] = DATE ( 2021, 6, 30 )
    )
)

     

    It is suggested to create relationships between Table A and Table B, and then you can use slicers to change the value dynamically like so:

     

    For more details, please check the attached .pbix file.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.