Forum Discussion
Value between two columns
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
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
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 |
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