Forum Discussion

samtheham's avatar
samtheham
Frequent Visitor
3 years ago

Creating a table with a dynamic filter that changes based on slicer

Hello all!

 

I am trying to  create a report that gives Sales figures by Fiscal Year and Fiscal Week from a Power BI dataset that has daily level data (I am doing a lot more, but for illustrative purposes let's say this). To do so, I have created an intermediary table using the following DAX query: 

 

week_table = ADDCOLUMNS(
    summarize('Master Table', 'Date Table'[Fiscal Year], 'Date Table'[Fiscal Period], 'Date Table'[Fiscal Week], "max_date", max('Date Table'[Transaction Date])), 
    "sales", [Sales]
)

 

This table query works fine, and allows me create a nice and responsive visual. However, there is a Company column in the 'Master Table' dataset, and I would like to create some sort of slicer that allows viewers to select a Company and update week_table so that it only gives Sales numbers for that specific company. For space purposes, I cannot bring Company directly into the table (it excees 1M rows and gives me an error). 

 

So far, I have tried the following: 

  1. Create a "company crosswalk" table that has the distinct company names. 
  2. Link the 'Master Table' table and the crosswalk by company name. 
  3. Create a slicer based on the company crosswalk table. 
  4. Update the DAX query as such: 
week_table = ADDCOLUMNS(
    summarize(FILTER('Master Table', 'Master Table'[Company] = SELECTEDVALUE(crosswalk[Opco Num])), 
    'Date Table'[Fiscal Year], 'Date Table'[Fiscal Period], 'Date Table'[Fiscal Week], "max_date", max('Date Table'[Transaction Date])), 
    "sales", [Sales]
)

 

I have also tried 'Master Table'[Company] IN VALUES crosswalk[Opco Num] in this statement. All have left me with a blank table with 0 rows. I feel like I am close... could someone please help me with this?

 

Thanks so much!

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    I don't understand what you need the intermediate table for. This is a job for your data model?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    You should create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  To your visual, drag year and Month name from the Calndar Table.  Write this measure

    Total = sum(Data[sales])

    Hope this helps.