Forum Discussion

JavierCM's avatar
JavierCM
Frequent Visitor
2 years ago
Solved

table filtered with a slicer

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?

 

 

Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

    • JavierCM's avatar
      JavierCM
      Frequent Visitor
      2 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.