table filtered with a slicer

[JavierCM]

Hi and thank you.

First of all I,m very new in power BI DAX expressions and now have a wall in front of me with this (I thought) easy step.

Maybe it is a very rokie question but I didn't find the solution.

I created a sample excel file with only two tables and very simple data to understand the problem and the solution. 

I have two tables :

1. Table "relaciones":

   • This table defines relationships between entities in the columns "primera zona" (first zone) and "segunda zona" (second zone).
   • Each relationship has a defined active period, starting from "sem inicio" (start week) to "sem final" (end week).
   • If "sem final" is blank, it indicates that the relationship is still ongoing and has not ended.

2. Table "serie":

   • This table contains a list of weeks, represented by the column "num", and their corresponding textual representation in the column "rec".
   • The "num" column is used to filter the data in the "relaciones" table based on the weeks selected in a slicer.

Objective

I want to create a filtered table from "relaciones" that displays only the relationships that were active during the weeks selected in the slicer (based on the "num" column from the "serie" table). The filtered table should include only those relationships where:

• The "sem inicio" (start week) is less than or equal to the selected weeks.
• The "sem final" (end week) is greater than or equal to the selected weeks or is blank (indicating the relationship is still active).

This filtered table will dynamically update based on the weeks selected in the slicer, showing only the relationships that were active during the selected period.

I've try with the new calculated table with the DAX: 

RelacionesFiltradas =
FILTER(
relaciones,
relaciones[sem inicio] <= MAXX(VALUES(serie[num]), serie[num]) &&
(ISBLANK(relaciones[sem final]) || relaciones[sem final] >= MINX(VALUES(serie[num]), serie[num]))
)

But dosen't work.

Can someboy help me?

[Anonymous · Accepted solution]

Hi @JavierCM ，hello lbendlin，thank you for your prompt reply!
For your requirements, please create a measure as shown below:

FilteredRows = 
   VAR SelectedMinWeek = MIN('series'[weeknum])
   VAR SelectedMaxWeek = MAX('series'[weeknum])
   RETURN
   CALCULATE(
      COUNTROWS(relationships),
      FILTER(
         relationships,
         relationships[start week] <= SelectedMaxWeek &&
         (ISBLANK(relationships[end week]) || relationships[end week] >= SelectedMinWeek)
      )
   )

 Then filter the table use the measure like this:

This allows us to dynamically filter the table with slicer values according to the desired logic:

Best regards,

Joyce

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

[Syndicate_Admin]

I actually want to understand how I can filter a table where there is a column with a start week number and another with an end week number, by means of the week numbers selected in a data segment. So that only the rows where the selected weeks are part of the active period appear. That is, the selected week is greater than or equal to the start week and less than or equal to the end week, or the end week is blank (not yet finished).

I have tried creating a measure with the value selected in the segmentation and using it in the filter function, but although the measure takes it well, then it does not filter and all the data comes out of the table.... No, I know, I think it should be easy but I can't find the key. I'm starting to think that dynamic table filtering may not be possible. Thank you very much for the help

[lbendlin]

Did you join the tables in the data model? (Hint: you shouldn't)

[JavierCM]

Thank you for your interest.

No. I want to selec a numbre and filter the table with this number, and thats all.

I know to use the command filter, but how can use with a variable and use a dynamic filtered table.  It's possible?

[lbendlin]

Yes. What have you tried and where are you stuck?

[JavierCM]

I'm trying to filter a table that establishes relationships between two data points in two columns. The table also has a column indicating the week when the relationship starts and another column for when it ends. I need to filter this relationship table based on the week selected in a data slicer. The goal is that when a specific week is selected, only the rows where the start week is earlier than the selected week and the end week is later remain visible.

I have tried various approaches, using FILTER directly, and also creating a measure to capture the selected week first... All of them fail, and all the rows keep appearing. I wonder if it's possible to filter tables dynamically.