cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
denisedf Frequent Visitor
Frequent Visitor

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

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)
2019-03-05 09_15_09-Power BI Desktop.jpg





























This is the relationship model:

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


model.jpg
















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.

2019-03-05 14_05_44-Power BI Desktop.jpg


1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

@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.

4 REPLIES 4
Community Support Team
Community Support Team

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

@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 Frequent Visitor
Frequent Visitor

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

@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):
2019-03-07 14_25_28-Power BI Desktop.jpg
Community Support Team
Community Support Team

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

@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.

Community Support Team
Community Support Team

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

@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