Value between two columns

[Anonymous]

I have a table that contains FROM and THROUGH serial number (not date) range values for a product line.

I would like to build a slicer that will find all rows where the input value with BETWEEN the FROM and THROUGH values. 

The table below shows what I am looking for. 

Ideally I would have a single range slider where I could specify a start and end range and any rows where the FROM and THROUGH overlapped at all would be included. 

The real world use cases are:

Show me all the parts that were contained in unit number 5. 

Show me all the parts that were contained in units 20 - 50. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[OwenAuger]

Hi @Anonymous 

I would recommend a measure using something similar to the Events in progress pattern.

Specifically the # Open Orders ALL measure in the above article.

If you have created a disconnected table containing values to use on a range slicer, with column called say Range[Value], you can then create a measure like this:

# Products in Range = 
VAR MinValue = MIN ( Range[Value] )
VAR MaxValue = MAX ( Range[Value] )
RETURN
    CALCULATE ( 
        COUNTROWS ( 'Product' ),
        'Product'[FROM] <= MaxValue,
        'Product'[THROUGH] >= MinValue
    )

Then if you have a visual that includes the PART column, you can apply a visual-level filter with # Products in Range is not blank or # Products in Range >= 1.

See attached PBIX for an example.

Regards,

Owen

[Anonymous]

@OwenAuger Thanks for the clear reply, I had tried something like that before but it was not filtering linked tables. I added another table (shown below) to your example and it does work so I must have missed something. This is very close to Nirvana for me, but none of the cards or graphs work with that measure as a filter so you can't show any useful summary data. 

I would send back a the pbix but the system won't let me attach. So, here is the scenario:

Added table shown below to the data model and linked to the original Part table. 

Added same table to the report and put the measure as a filter. 

Added pie chart with Part as the legend and count of Child as the value. 

Adjust the slider and see the tables in the report adjust but not the graphs. 

Any ideas? 

Part    Child

A	A1
A	A2
A	A3
B	B1
C	C1
C	C2
D	D1
D	D2
E	E1
F	F1
G	G1

[OwenAuger · Accepted solution]

Hi @Anonymous 

EDIT: Forgot the attachment - just attached it!

Thanks for testing this out!

After looking at your overall requirements, I think this is a good candidate for a Calculation Group, with a Calculation Item that applies the range filter to the parts .

When the Calculation Item is applied as a filter, the "range" filter will be applied to any measures used in the visuals.

I have attached an updated PBIX, with a PartChild table added (hopefully similar to yours).

1. The first thing I did was create a Calculation Group called Parts Filter, with a single column Parts Filter, with a Calculation Item Parts in Range. This Calculation Item is defined as

VAR MinValue =
    MIN ( Range[Value] )
VAR MaxValue =
    MAX ( Range[Value] )
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        'Product'[FROM] <= MaxValue,
        'Product'[THROUGH] >= MinValue
    )

If you haven't created Calculation Groups before, you'll need Tabular Editor. See here for example.

2. Next I applied a Page filter, selecting Parts Filter = "Parts in Range".

3. Then I ensured that all visuals use explicit measures, namely:

Part Count = 
COUNTROWS ( 'Product' )

Child Count = 
COUNTROWS ( PartChild )

4. The above measures work fine on cards/pie charts. However table visuals with no measures displayed need a have a measure injected somehow. I chose to apply a visual level filter of Part Count is not blank to the table visuals, which is modified by the Calculation Gruop.

What do you think of this method? I think it simplifies applying the range filter and saves writing multiple measures with the same filter.

Regards,

Owen

[dkaushik]

You don't need to I guess. You can simply use the other table's single column and the you can choose your own from and to values directly from the slicer. 

[Anonymous]

Sorry, still being dense here. I don't understand how to "use the other table's single column"
Assume this simplistic table structure: 

Putting slicers simply on the FROM and THROUGH values does not yield what I need. 
Do I build a relationship between the two tables? 

I have tried using the <= and >= options on separate slicers and configured them either way and it does not return all the rows that overlap the specified range. 

A single slicer on either column won't do it either. If I put in 10 as in input value I would want to see rows that have 10 anywhere between the FROM and THROUGH. 

Thanks for your responses. 

[dkaushik]

Hi @Anonymous ,

You can achieve this by using a single column where you have values from 1 to max range. Then use that column in a slicer and use the option "Between" from the downward arrow in top right, as shown below:

Thanks,

Dheeraj

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

[Anonymous]

Thanks, but I don't really understand how to put the pieces together. I do have another table that has a single column I can leverage but I can't connect this table to it with both the FROM and THROUGH values since that would be multiple relationships.