Forum Discussion
Resolver IReturn minimum/maximum of all groups across date range
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.
Wendeley-NorthHi 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
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):
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%" )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.
- Wendeley-North
Bump.