Forum Discussion

hani1711's avatar
hani1711
Regular Visitor
1 year ago
Solved

how to filter table based on another table

i have a table like in the picture, with relationship route per week to sales and to stock with column cust id, and relationship sales to master route with column cust id too

i have a calculated column in table route per week:
check sales = to check in table sales within same customer, year, and week if there is any row match
sales 8 week = to check 8 week back if there is any sales (ex: in week 42 will see column check sales within week 35 until 42 if there is any value)
check stock = to check in table stock within same customer, year, and week if there is any row match 

stock 8 week = to check 8 week back if there is any stock (ex: in week 42 will see column check stock within week 35 until 42 if there is any value)
distributive = if sales8week is 0 and stock8week is 0, then 0, else 1

i have column chart by sum(distributive) and by year and week, I want to add 3 slicer to filter based on table master route column type and sch id and slicer based on item id in table sales, how can i do that? should i change the relation or is there anything else i can do? because i have tried add slicer by item id, but the chart didnt showing anything

Need Help

2 Replies

  • saud968's avatar
    saud968
    Memorable Member
    1 year ago

    To solve this:

    Review current relationships:
    Ensure that all necessary relationships are in place and correctly configured. You mentioned relationships between:

    Route per week to Sales (on Cust ID)
    Route per week to Stock (on Cust ID)
    Sales to Master Route (on Cust ID)


    Add missing relationships:
    You may need to add a relationship between Sales and Route per week on both Cust ID and Week/Year if it doesn't exist.
    Create a Date table:
    If you don't have one already, create a date table with Year and Week columns. This will help in time-based calculations and filtering.
    Adjust the Distributive measure:
    Instead of a calculated column, create a measure for Distributive. This will be more flexible for filtering:
    CopyDistributive =
    VAR Sales8Week = CALCULATE(COUNTROWS(Sales), DATESINPERIOD(Date[Date], MAX(Date[Date]), -56, DAY))
    VAR Stock8Week = CALCULATE(COUNTROWS(Stock), DATESINPERIOD(Date[Date], MAX(Date[Date]), -56, DAY))
    RETURN
    IF(Sales8Week = 0 && Stock8Week = 0, 0, 1)

    Create a measure for the chart:
    CopyChart Measure = SUM(Route_per_week[Distributive])

    Add slicers:

    Add a slicer for Master Route[Type]
    Add a slicer for Master Route[Sch ID]
    Add a slicer for Sales[Item ID]


    Use TREATAS or CROSSFILTER:
    To make the Item ID slicer work correctly, you might need to use TREATAS or CROSSFILTER in your measure. Here's an example:
    CopyChart Measure with Item Filter =
    CALCULATE(
    [Chart Measure],
    TREATAS(VALUES(Sales[Item ID]), 'Route per week'[Item ID])
    )
    This assumes you have an Item ID column in your Route per week table. If not, you might need to add it or adjust the relationships.
    Create a Bridge table:
    If the above doesn't work, you might need to create a bridge table that connects Item ID to your Route per week table. This table would include combinations of Cust ID, Week, Year, and Item ID.

    Here's how you could implement this in Power BI:
    DAX Measure for Distributive Chart with SlicersClick to open code
    To use this measure:

    Create the measure in your Power BI model.
    Use this measure in your column chart instead of the original Chart Measure.
    Add slicers for Master Route[Type], Master Route[Sch ID], and Sales[Item ID].

    This solution assumes that 'Route per week' table has columns for Item ID, Type, and Sch ID. If it doesn't, you'll need to add these columns or adjust the relationships in your data model.



    Best Regards
    Saud Ansari
    If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from saud968 , please allow me to provide another insight: 
    Hi  hani1711 ,

     

    You might consider using the master route table as a bridge table. Connect the other tables to the master route table using [cust id]. Set the filter direction to both. This way, tables can filter each other. If table A and table B are connected with a single direction towards table B, then table A can filter table B in visuals, but table B cannot filter table A.

    Many-to-many relationship guidance - Power BI | Microsoft Learn

    Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

    Model relationships in Power BI Desktop - Power BI | Microsoft Learn

    Slicer item:

     

    Best Regards,

    Liu Yang

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