Hi, this builds upon an earlier thread that I created here, hence the familiar looking title. Last I left off, we had solved the issue of pulling individual historical minimums and maximums based on groups, i.e.:For a particular group provided by row context, find the last two months worth of data.For each date within the last two months, dateSum = SUM ( [Value] ).Return MIN ( dateSum ) for group.Now, I would like to get the minimum for all groups, i.e.:Find last two months worth of data.For each date within the last two groups, dateSum = SUM ( [Value] ) for all groupsReturn MIN ( dateSum ) across all the groups, i.e. only 1 valueUpdate 1 June 21 : This needs to work at all levels of drilldown, from [Group] to [Sub_Sub_Sub_Group]. Please refer to Message 2 to see my failed attempt at using ISINSCOPE().I guess providing illustrations may be easier (Sample data and used measures provided after image):According to the picture above, the new measure will return the minimum of (-0.06%, -0.05%) = -0.06%, i.e. the column formed by the measure will return only one value for all groups. I will need this to work with drilldown present for the groups present.Sample DataSlicerGroup1DateSlicerGroup2IDValueGroupSub_GroupSub_Sub_GroupSub_Sub_Sub_GroupSG1_131-Mar-21SG2_1ID_1-0.07000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_131-Mar-21SG2_1ID_2-0.08000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_131-Mar-21SG2_1ID_30.05000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_130-Apr-21SG2_1ID_30.06000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_130-Apr-21SG2_1ID_1-0.01000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_130-Apr-21SG2_1ID_2-0.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_231-Mar-21SG2_1ID_4-0.01000%Group_2G2_SG1G2_SG1_SSG1G2_SG1_SSG1_SSSG1SG1_104-May-21SG2_1ID_2-0.09000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_103-May-21SG2_1ID_2-0.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_104-May-21SG2_1ID_10.06000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_103-May-21SG2_1ID_1-0.05000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_104-May-21SG2_1ID_30.01000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_103-May-21SG2_1ID_3-0.06000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_230-Apr-21SG2_1ID_4-0.05000%Group_2G2_SG1G2_SG1_SSG1G2_SG1_SSG1_SSSG1SG1_231-Mar-21SG2_1ID_1-0.02000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_231-Mar-21SG2_1ID_30.03000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_231-Mar-21SG2_1ID_20.03000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_230-Apr-21SG2_1ID_3-0.03000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_230-Apr-21SG2_1ID_1-0.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_230-Apr-21SG2_1ID_20.01000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_203-May-21SG2_1ID_20.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_204-May-21SG2_1ID_20.00000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_204-May-21SG2_1ID_10.00000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_203-May-21SG2_1ID_10.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_204-May-21SG2_1ID_30.05000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_203-May-21SG2_1ID_30.04000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_204-May-21SG2_1ID_40.03000%Group_2G2_SG1G2_SG1_SSG1G2_SG1_SSG1_SSSG1SG1_203-May-21SG2_1ID_4-0.05000%Group_2G2_SG1G2_SG1_SSG1G2_SG1_SSG1_SSSG1Measures Shown 2m_min_vera = VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 1-year lookback VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) VAR filteredTable = FILTER ( ALLEXCEPT( Table2, Table2[Group], Table2[Sub_Group], Table2[Sub_Sub_Group], Table2[Sub_Sub_Sub_Group] ), -- Create a new table, ignoring the Group row contexts Table2[SlicerGroup1] IN slicerSelection && Table2[Date] >= startDate && Table2[Date] <= endDate ) VAR t1 = SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] )) RETURN -- CONCATENATEX( t1, Table2[Group], "," ) -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) FORMAT( MINX ( t1, [sumQuantity]), "0.00%" ) Appreciate any assistance, thank you.

Hi, I've figured a partial solution that works on a manually defined category, i.e. Table2[Group] basis. However, given that this measure will have to work on drill-downs, I can't quite seem to get it to work using ISINSCOPE(). The current working code that gives the output I want:Relevant 2 measures used: 2m_min = VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) VAR filteredTable = FILTER ( ALLEXCEPT( Table2, Table2[Group], Table2[Sub_Group], Table2[Sub_Sub_Group], Table2[Sub_Sub_Sub_Group] ), -- Create a new table, ignoring the Group row contexts Table2[SlicerGroup1] IN slicerSelection && Table2[Date] >= startDate && Table2[Date] <= endDate ) VAR t1 = SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] )) RETURN -- CONCATENATEX( t1, Table2[Group], "," ) -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )2m_min_all_sectors = VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) VAR filteredTable = FILTER ( ALL( Table2 ), -- Create a new table, ignoring the Group row contexts Table2[SlicerGroup1] IN slicerSelection && Table2[Date] >= startDate && Table2[Date] <= endDate ) VAR t1 = SUMMARIZE(filteredTable, Table2[Date], Table2[Group], "sumQuantity", SUM ( [Value] )) RETURN -- CONCATENATEX( t1, Table2[Group], "," ) -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) FORMAT( MINX ( t1, [sumQuantity]), "0.00%" ) ISINSCOPE() that returns an error: 2m_min_all_sectors = VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) VAR filteredTable = FILTER ( ALL( Table2 ), -- Create a new table, ignoring the Group row contexts Table2[SlicerGroup1] IN slicerSelection && Table2[Date] >= startDate && Table2[Date] <= endDate ) VAR t1 = SWITCH( TRUE(), ISINSCOPE( Table2[Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Group], "sumQuantity", SUM ( [Value] )), ISINSCOPE( Table2[Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Group], "sumQuantity", SUM ( [Value] )), ISINSCOPE( Table2[Sub_Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Sub_Group], "sumQuantity", SUM ( [Value] )), ISINSCOPE( Table2[Sub_Sub_Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Sub_Sub_Group], "sumQuantity", SUM ( [Value] )) ) RETURN -- CONCATENATEX( t1, Table2[Group], "," ) -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

Hi Wendeley-North , Here are the steps you can follow： 1. Create measure. Measure = MINX( ALLSELECTED('Table2'[Group]),[2m_min_vera]) 2. Result: Best Regards, Liu Yang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply, but this seems to only work on a [Group] level, but I'll need it to work at all levels of drill down, i.e. [Group], [Sub Group], ... [Sub Sub Sub Group]

Hi,I would like to get the minimum for all groups, i.e.:Find last two months worth of data.For each date within the last two groups, dateSum = SUM ( [Value] ) for all groupsReturn MIN ( dateSum ) across all the groups, i.e. only 1 valueThis needs to work at all levels of drilldown, from [Group] to [Sub_Sub_Sub_Group]. I have working code if it is [Group] only. Please refer to below to see my failed attempt at using ISINSCOPE().I guess providing illustrations may be easier (Sample data and used measures provided after image):According to the picture above, the new measure will return the minimum of (-0.06%, -0.05%) = -0.06%, i.e. the column formed by the measure will return only one value for all groups. I will need this to work with drilldown present for the groups present.Sample DataSlicerGroup1DateSlicerGroup2IDValueGroupSub_GroupSub_Sub_GroupSub_Sub_Sub_GroupSG1_131-Mar-21SG2_1ID_1-0.07000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_131-Mar-21SG2_1ID_2-0.08000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_131-Mar-21SG2_1ID_30.05000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_130-Apr-21SG2_1ID_30.06000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_130-Apr-21SG2_1ID_1-0.01000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_130-Apr-21SG2_1ID_2-0.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_231-Mar-21SG2_1ID_4-0.01000%Group_2G2_SG1G2_SG1_SSG1G2_SG1_SSG1_SSSG1SG1_104-May-21SG2_1ID_2-0.09000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_103-May-21SG2_1ID_2-0.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_104-May-21SG2_1ID_10.06000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_103-May-21SG2_1ID_1-0.05000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_104-May-21SG2_1ID_30.01000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_103-May-21SG2_1ID_3-0.06000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_230-Apr-21SG2_1ID_4-0.05000%Group_2G2_SG1G2_SG1_SSG1G2_SG1_SSG1_SSSG1SG1_231-Mar-21SG2_1ID_1-0.02000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_231-Mar-21SG2_1ID_30.03000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_231-Mar-21SG2_1ID_20.03000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_230-Apr-21SG2_1ID_3-0.03000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_230-Apr-21SG2_1ID_1-0.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_230-Apr-21SG2_1ID_20.01000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_203-May-21SG2_1ID_20.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_204-May-21SG2_1ID_20.00000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG2SG1_204-May-21SG2_1ID_10.00000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_203-May-21SG2_1ID_10.04000%Group_1G1_SG1G1_SG1_SSG1G1_SG1_SSG1_SSSG1SG1_204-May-21SG2_1ID_30.05000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_203-May-21SG2_1ID_30.04000%Group_1G1_SG2G1_SG2_SSG1G1_SG2_SSG1_SSSG1SG1_204-May-21SG2_1ID_40.03000%Group_2G2_SG1G2_SG1_SSG1G2_SG1_SSG1_SSSG1SG1_203-May-21SG2_1ID_4-0.05000%Group_2G2_SG1G2_SG1_SSG1G2_SG1_SSG1_SSSG1Measures Shown 2m_min_vera = VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 1-year lookback VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) VAR filteredTable = FILTER ( ALLEXCEPT( Table2, Table2[Group], Table2[Sub_Group], Table2[Sub_Sub_Group], Table2[Sub_Sub_Sub_Group] ), -- Create a new table, ignoring the Group row contexts Table2[SlicerGroup1] IN slicerSelection && Table2[Date] >= startDate && Table2[Date] <= endDate ) VAR t1 = SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] )) RETURN -- CONCATENATEX( t1, Table2[Group], "," ) -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) FORMAT( MINX ( t1, [sumQuantity]), "0.00%" ) Working Code for [Group] onlyIt gives the output I want:Measure for 2m_min_all_sectors [2m_min is the same as 2m_min_vera above]: 2m_min_all_sectors = VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) VAR filteredTable = FILTER ( ALL( Table2 ), -- Create a new table, ignoring the Group row contexts Table2[SlicerGroup1] IN slicerSelection && Table2[Date] >= startDate && Table2[Date] <= endDate ) VAR t1 = SUMMARIZE(filteredTable, Table2[Date], Table2[Group], "sumQuantity", SUM ( [Value] )) RETURN -- CONCATENATEX( t1, Table2[Group], "," ) -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) FORMAT( MINX ( t1, [sumQuantity]), "0.00%" ) ISINSCOPE() that returns an error: 2m_min_all_sectors = VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) VAR filteredTable = FILTER ( ALL( Table2 ), -- Create a new table, ignoring the Group row contexts Table2[SlicerGroup1] IN slicerSelection && Table2[Date] >= startDate && Table2[Date] <= endDate ) VAR t1 = SWITCH( TRUE(), ISINSCOPE( Table2[Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Group], "sumQuantity", SUM ( [Value] )), ISINSCOPE( Table2[Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Group], "sumQuantity", SUM ( [Value] )), ISINSCOPE( Table2[Sub_Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Sub_Group], "sumQuantity", SUM ( [Value] )), ISINSCOPE( Table2[Sub_Sub_Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Sub_Sub_Group], "sumQuantity", SUM ( [Value] )) ) RETURN -- CONCATENATEX( t1, Table2[Group], "," ) -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) FORMAT( MINX ( t1, [sumQuantity]), "0.00%" ) Appreciate any assistance, thank you.

Forum Discussion

Wendeley-North

Resolver I

5 years ago

Return minimum/maximum of all groups across date range

Hi, this builds upon an earlier thread that I created here, hence the familiar looking title.

Last I left off, we had solved the issue of pulling individual historical minimums and maximums based on groups, i.e.:

For a particular group provided by row context, find the last two months worth of data.
For each date within the last two months, dateSum = SUM ( [Value] ).
Return MIN ( dateSum ) for group.

Now, I would like to get the minimum for all groups, i.e.:

Find last two months worth of data.
For each date within the last two groups, dateSum = SUM ( [Value] ) for all groups
Return MIN ( dateSum ) across all the groups, i.e. only 1 value
- Update 1 June 21 : This needs to work at all levels of drilldown, from [Group] to [Sub_Sub_Sub_Group]. Please refer to Message 2 to see my failed attempt at using ISINSCOPE().

I guess providing illustrations may be easier (Sample data and used measures provided after image):

According to the picture above, the new measure will return the minimum of (-0.06%, -0.05%) = -0.06%, i.e. the column formed by the measure will return only one value for all groups. I will need this to work with drilldown present for the groups present.

Sample Data

SlicerGroup1	Date	SlicerGroup2	ID	Value	Group	Sub_Group	Sub_Sub_Group	Sub_Sub_Sub_Group
SG1_1	31-Mar-21	SG2_1	ID_1	-0.07000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	31-Mar-21	SG2_1	ID_2	-0.08000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	31-Mar-21	SG2_1	ID_3	0.05000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_3	0.06000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_1	-0.01000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_2	-0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	31-Mar-21	SG2_1	ID_4	-0.01000%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_1	04-May-21	SG2_1	ID_2	-0.09000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	03-May-21	SG2_1	ID_2	-0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	04-May-21	SG2_1	ID_1	0.06000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	03-May-21	SG2_1	ID_1	-0.05000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	04-May-21	SG2_1	ID_3	0.01000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	03-May-21	SG2_1	ID_3	-0.06000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_4	-0.05000%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_1	-0.02000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_3	0.03000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_2	0.03000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	30-Apr-21	SG2_1	ID_3	-0.03000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_1	-0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_2	0.01000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	03-May-21	SG2_1	ID_2	0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	04-May-21	SG2_1	ID_2	0.00000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	04-May-21	SG2_1	ID_1	0.00000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	03-May-21	SG2_1	ID_1	0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	04-May-21	SG2_1	ID_3	0.05000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	03-May-21	SG2_1	ID_3	0.04000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	04-May-21	SG2_1	ID_4	0.03000%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_2	03-May-21	SG2_1	ID_4	-0.05000%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1

Measures Shown

2m_min_vera = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 1-year lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) 
VAR filteredTable = 
    FILTER (
        ALLEXCEPT( Table2, Table2[Group], Table2[Sub_Group], Table2[Sub_Sub_Group], Table2[Sub_Sub_Sub_Group] ), -- Create a new table, ignoring the Group row contexts
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] ))
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," )
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) 
    FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

Appreciate any assistance, thank you.

Need Help

10 Replies

Wendeley-North

Resolver I

5 years ago

Hi, I've figured a partial solution that works on a manually defined category, i.e. Table2[Group] basis. However, given that this measure will have to work on drill-downs, I can't quite seem to get it to work using ISINSCOPE().

The current working code that gives the output I want:

Relevant 2 measures used:

2m_min = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) 
VAR filteredTable = 
    FILTER (
        ALLEXCEPT( Table2, Table2[Group], Table2[Sub_Group], Table2[Sub_Sub_Group], Table2[Sub_Sub_Sub_Group] ), -- Create a new table, ignoring the Group row contexts
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] ))
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," )
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) 
    FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

2m_min_all_sectors = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) 
VAR filteredTable = 
    FILTER (
        ALL( Table2 ), -- Create a new table, ignoring the Group row contexts
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SUMMARIZE(filteredTable, Table2[Date], Table2[Group], "sumQuantity", SUM ( [Value] ))
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," )
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) 
    FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

ISINSCOPE() that returns an error:

2m_min_all_sectors = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) 
VAR filteredTable = 
    FILTER (
        ALL( Table2 ), -- Create a new table, ignoring the Group row contexts
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SWITCH(
        TRUE(),
        ISINSCOPE( Table2[Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Group], "sumQuantity", SUM ( [Value] )), 
        ISINSCOPE( Table2[Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Group], "sumQuantity", SUM ( [Value] )), 
        ISINSCOPE( Table2[Sub_Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Sub_Group], "sumQuantity", SUM ( [Value] )), 
        ISINSCOPE( Table2[Sub_Sub_Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Sub_Sub_Group], "sumQuantity", SUM ( [Value] ))
    )
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," )
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) 
    FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

Wendeley-North
Resolver I
5 years ago
Bump

Anonymous
Not applicable
5 years ago
Hi Wendeley-North ,

Here are the steps you can follow：

1. Create measure.

Measure = MINX( ALLSELECTED('Table2'[Group]),[2m_min_vera])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Return minimum maximum of all groups across date range.pbix42 KB
- Wendeley-North
  Resolver I
  5 years ago
  Thanks for the reply, but this seems to only work on a [Group] level, but I'll need it to work at all levels of drill down, i.e. [Group], [Sub Group], ... [Sub Sub Sub Group]
Wendeley-North
Resolver I
5 years ago
Bump.

Anonymous

Not applicable

5 years ago

Hi Wendeley-North ,

The last line of measure[2m_min_vera] has been modified and can be used for comparison.

2m_min_vera =
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 1-year lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] )
VAR filteredTable =
    FILTER (
        ALLEXCEPT( Table2, Table2[Group], Table2[Sub_Group], Table2[Sub_Sub_Group], Table2[Sub_Sub_Sub_Group] ), -- Create a new table, ignoring the Group row contexts
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] ))
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," )
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," )
    //FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )
MINX( t1, [sumQuantity])

You can add multiple fields to allselected().

Measure =
MINX( ALLSELECTED('Table2'[Group],'Table2'[Sub_Group],'Table2'[Sub_Sub_Group],'Table2'[Sub_Sub_Sub_Group]),[2m_min_vera])

But after discovering, when'Table2'[Group],'Table2'[Sub_Group],'Table2'[Sub_Sub_Group],'Table2'[Sub_Sub_Sub_Group] exists, the minimum value is -0.05.

There is only one Table2'[Group], the minimum value is -0.06.

Best Regards,

Liu Yang

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

234Return minimummaximum of all groups across date range.pbix33 KB

Wendeley-North

Resolver I

5 years ago

Hi that's not quite what I am looking for, but thanks for the reply.

In this image where the groups are not drilled down, the measure should return -0.06%. It has to figure out at which level in the drill down it is, and calculate the minimum within the hierarchy respectively.

To provide a further example:

In the event where the visual is fully drilled down (something like the above, but imagine that the only columns are [Sub_Sub_Sub_Group], the measure should return -0.05% for the whole column.

Wendeley-North

Resolver I

5 years ago

Hi,

I would like to get the minimum for all groups, i.e.:

Find last two months worth of data.
For each date within the last two groups, dateSum = SUM ( [Value] ) for all groups
Return MIN ( dateSum ) across all the groups, i.e. only 1 value
This needs to work at all levels of drilldown, from [Group] to [Sub_Sub_Sub_Group]. I have working code if it is [Group] only. Please refer to below to see my failed attempt at using ISINSCOPE().

I guess providing illustrations may be easier (Sample data and used measures provided after image):

Sample Data

SlicerGroup1	Date	SlicerGroup2	ID	Value	Group	Sub_Group	Sub_Sub_Group	Sub_Sub_Sub_Group
SG1_1	31-Mar-21	SG2_1	ID_1	-0.07000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	31-Mar-21	SG2_1	ID_2	-0.08000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	31-Mar-21	SG2_1	ID_3	0.05000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_3	0.06000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_1	-0.01000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	30-Apr-21	SG2_1	ID_2	-0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	31-Mar-21	SG2_1	ID_4	-0.01000%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_1	04-May-21	SG2_1	ID_2	-0.09000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	03-May-21	SG2_1	ID_2	-0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_1	04-May-21	SG2_1	ID_1	0.06000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	03-May-21	SG2_1	ID_1	-0.05000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_1	04-May-21	SG2_1	ID_3	0.01000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_1	03-May-21	SG2_1	ID_3	-0.06000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_4	-0.05000%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_1	-0.02000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_3	0.03000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	31-Mar-21	SG2_1	ID_2	0.03000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	30-Apr-21	SG2_1	ID_3	-0.03000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_1	-0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	30-Apr-21	SG2_1	ID_2	0.01000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	03-May-21	SG2_1	ID_2	0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	04-May-21	SG2_1	ID_2	0.00000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG2
SG1_2	04-May-21	SG2_1	ID_1	0.00000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	03-May-21	SG2_1	ID_1	0.04000%	Group_1	G1_SG1	G1_SG1_SSG1	G1_SG1_SSG1_SSSG1
SG1_2	04-May-21	SG2_1	ID_3	0.05000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	03-May-21	SG2_1	ID_3	0.04000%	Group_1	G1_SG2	G1_SG2_SSG1	G1_SG2_SSG1_SSSG1
SG1_2	04-May-21	SG2_1	ID_4	0.03000%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1
SG1_2	03-May-21	SG2_1	ID_4	-0.05000%	Group_2	G2_SG1	G2_SG1_SSG1	G2_SG1_SSG1_SSSG1

Measures Shown

2m_min_vera = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 1-year lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) 
VAR filteredTable = 
    FILTER (
        ALLEXCEPT( Table2, Table2[Group], Table2[Sub_Group], Table2[Sub_Sub_Group], Table2[Sub_Sub_Sub_Group] ), -- Create a new table, ignoring the Group row contexts
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SUMMARIZE(filteredTable, Table2[Date], "sumQuantity", SUM ( [Value] ))
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," )
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) 
    FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

Working Code for [Group] only

It gives the output I want:

Measure for 2m_min_all_sectors [2m_min is the same as 2m_min_vera above]:

2m_min_all_sectors = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) 
VAR filteredTable = 
    FILTER (
        ALL( Table2 ), -- Create a new table, ignoring the Group row contexts
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SUMMARIZE(filteredTable, Table2[Date], Table2[Group], "sumQuantity", SUM ( [Value] ))
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," )
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) 
    FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

ISINSCOPE() that returns an error:

2m_min_all_sectors = 
VAR endDate = LASTDATE(Table2[Date]) -- Determined by slicer
VAR startDate = DATE( YEAR(endDate), MONTH(endDate) -2, DAY(endDate) ) -- 2-month lookback
VAR slicerSelection = VALUES( Table2[SlicerGroup1] ) 
VAR filteredTable = 
    FILTER (
        ALL( Table2 ), -- Create a new table, ignoring the Group row contexts
        Table2[SlicerGroup1] IN slicerSelection &&
        Table2[Date] >= startDate &&
        Table2[Date] <= endDate
    )
VAR t1 =
    SWITCH(
        TRUE(),
        ISINSCOPE( Table2[Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Group], "sumQuantity", SUM ( [Value] )), 
        ISINSCOPE( Table2[Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Group], "sumQuantity", SUM ( [Value] )), 
        ISINSCOPE( Table2[Sub_Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Sub_Group], "sumQuantity", SUM ( [Value] )), 
        ISINSCOPE( Table2[Sub_Sub_Sub_Group] ), SUMMARIZE(filteredTable, Table2[Date], Table2[Sub_Sub_Sub_Group], "sumQuantity", SUM ( [Value] ))
    )
RETURN
    -- CONCATENATEX( t1, Table2[Group], "," )
    -- CONCATENATEX( t1, FORMAT([sumQuantity], "0.00%"), "," ) 
    FORMAT( MINX ( t1, [sumQuantity]), "0.00%" )

Appreciate any assistance, thank you.

Anonymous
Not applicable
5 years ago
Hi Wendeley-North ,

Here are the steps you can follow：

1. Create measure.

Measure = var _table=SUMMARIZE(ALL('Table2'),[Date],[Group],"1",[2m_min_vera]) var _min=MINX(_table,[1]) return _min

2. Result：

Is this answer what you need? Works in all levels of drilling.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
234Return minimummaximum of all groups across date range.pbix35 KB

Forum Discussion

Return minimum/maximum of all groups across date range

10 Replies

Related content

Average of minimum/maximum

Conditional Formatting when minimum and maximum are the same

Select a value from minimum and maximum date by group

Color gradient, dynamic minimum and maximum values

Fabric & Azure Data User Group Program Requirements & Benefits