Slicer that filters table content within a range existing in 2 columns

[denisedf]

Hi everyone,

I am super new to Power BI and DAX. Would like to see if anyone can help me with this.

I have a table called modsum_view which contains column FROMPSN and TOPSN.

The idea is having a slicer where the user will select a number to filter down to selection.

In SQL the query would be:

SELECT * FROM modsum_view
WHERE
FROMPSN <= 50020 AND 50020 <= TOPSN

*in the example above 50020 is what user selected in the slicer.

The table containing the data would then display only the data where 50020 exists in the FROMPSN/TOPSN range. (highlighed below)

This is the relationship model:

Tails[Tails] to Modsum_view[Frompsn]
and
Tails[Tails] to Modsum_view[Topsn] (inactive)



















The Tails table has a column which contains all possible numbers that can be used for filtering down Modsum_view table (columns Frompsn and Topsn). It was generated using the what if / generatedseries

Tails = SELECTCOLUMNS(GENERATESERIES(50001, 59999,1),"Tails", INT([Value]))

In the Selected tail is where I am trying to get the selected tail to filter the modsum_view data but I am stuck.

I think I am missing USERELATIONSHIP(Tails[Tails],MODSUM_VIEW[TOPSN]) to activate the relationship tails and the filter needs to be within an aggregation function, but I cannot get this sorted out.

Any help is much appreciated.

[v-yuta-msft]

@denisedf ,

Click Modeling-> New Table using DAX below and check if they can meet your requirement:

Filtered_Table =
FILTER (
    modsum_view,
    modsum_view[FROMPSN ] <= SELECTEDVALUE ( Table[selection] )
        && modsum_view[TOPSN] >= SELECTEDVALUE ( Table[selection] )
)

Or

Filtered_Table =
CALCULATETABLE (
    modsum_view,
    modsum_view[FROMPSN ] <= SELECTEDVALUE ( Table[selection] )
        && modsum_view[TOPSN] >= SELECTEDVALUE ( Table[selection] )
)

Community Support Team _ Jimmy Tao

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

[denisedf]

@v-yuta-msft  Thank you very much for helpign me.

I am trying to use the solution you suggested:

Filtered_Table =

    FILTER (

    modsum_view,

     modsum_view[FROMPSN] <= Tails[Selectedtail]

     && modsum_view[TOPSN] >= Tails[Selectedtail]

    )

I created the filtered table as per your suggestion. My trouble now is the relationship between these tables and how to pass the selected value from the slicer onto the filtered table.

In my previous relationship model  I had:

Tails[Tails] 1 to * Modsum_view[FromPSN] (active) and
Tails[Tails] 1 to * Modsum_view[ToPSN] (inactive)

In Tails table:

Tails = SELECTCOLUMNS(GENERATESERIES(50001, 59999,1),"Tails", INT([Value]))

and 

Selectedtail = SELECTEDVALUE('Tails'[Tails])

I am kind of lost how to make the relationship between this new table (Filtered_Table) and the table that contains the slicer and how to make the slice selection also be used by inactive relationship in modsum_view[TOPSN] >= Tails[Selectedtail] .

Below is what the relationships currently look like (with comments):

[v-yuta-msft · Accepted solution]

@denisedf ,

You may created a relationship between the slicer table and filtered table in one-many or one-one relationship, then use condition like Filtered[TOPSN] >= Related(Tails[Selectedtail]).

Community Support Team _ Jimmy Tao

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

[v-yuta-msft]

@denisedf ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao