Compute operations with values of subsamples of columns that respond dynamically to slicers

[Javier_Arze]

I have a dataset that has four columns: Country, Variable, Value, Year, and Vintage. The Country denotes country names of different 69 countries, Variables are 40 economic, Value is the actual numerical value for each variable, the year ranges from 2019-2028 and the Vintage is a text that  denotes a date in which a set of values was published (say “June 2023”, we two Vintages per year). So a short extract of the dataset in the first table below

The  objective is to set a page in which the user can select: 1) a Variable, and 2) two Vintages for which we need to compute the difference in the Values. For example, in the table above if the user selected the Variable: “GDP growth”, Vintage 1 = October 2020, and Vintage 2 = Oct 2021, the resulting column should list, for all the countries in the data, a column with the difference in the values of these two vintages for each year. 

This would be straightforward by using calculated columns and by reshaping the data so each Vintage is shown in a different column but we have too many vintages to create one for column for each and all the potential differences between the options. Hence, we would like the ability of a measure to responds dynamically to the filter context defined by the user’s selections of Variable and Vintages, but the result should still be a column not a single aggregate value.  I am not sure if this can be achieved in some way. 

I have attached below a small extract of the data in the first table, and one example of how the result would look like (in the thrird Table below) , given three selections of variable, and the two vintages a used may want to select using the slicers in third table (second table). Any advice woudl be greatly appreciated.    

Code	year	Variable	Value	vintage
196	2019	Debt Public debt	32	WEO_Oct2022
196	2019	Real GDP Growth	3	WEO_Oct2022
196	2020	Debt Public debt	43	WEO_Oct2022
196	2020	Real GDP Growth	-2	WEO_Oct2022
196	2021	Debt Public debt	51	WEO_Oct2022
196	2021	Real GDP Growth	6	WEO_Oct2022
199	2019	Debt Public debt	56	WEO_Oct2022
199	2019	Real GDP Growth	0	WEO_Oct2022
199	2020	Debt Public debt	69	WEO_Oct2022
199	2020	Real GDP Growth	-6	WEO_Oct2022
199	2021	Debt Public debt	69	WEO_Oct2022
199	2021	Real GDP Growth	5	WEO_Oct2022
213	2019	Debt Public debt	89	WEO_Oct2022
213	2019	Real GDP Growth	-2	WEO_Oct2022
213	2020	Debt Public debt	103	WEO_Oct2022
213	2020	Real GDP Growth	-10	WEO_Oct2022
213	2021	Debt Public debt	81	WEO_Oct2022
213	2021	Real GDP Growth	10	WEO_Oct2022
218	2019	Debt Public debt	59	WEO_Oct2022
218	2019	Real GDP Growth	2	WEO_Oct2022
218	2020	Debt Public debt	78	WEO_Oct2022
218	2020	Real GDP Growth	-9	WEO_Oct2022
218	2021	Debt Public debt	80	WEO_Oct2022
218	2021	Real GDP Growth	6	WEO_Oct2022
196	2019	Fiscal Rev.Tax.Income	18	WEO_Oct2022
196	2020	Fiscal Rev.Tax.Income	19	WEO_Oct2022
196	2021	Fiscal Rev.Tax.Income	19	WEO_Oct2022
199	2019	Fiscal Rev.Tax.Income	14	WEO_Oct2022
199	2020	Fiscal Rev.Tax.Income	13	WEO_Oct2022
199	2021	Fiscal Rev.Tax.Income	14	WEO_Oct2022
213	2019	Fiscal Rev.Tax.Income	5	WEO_Oct2022
213	2020	Fiscal Rev.Tax.Income	5	WEO_Oct2022
213	2021	Fiscal Rev.Tax.Income	5	WEO_Oct2022
218	2019	Fiscal Rev.Tax.Income	WEO_Oct2022
218	2020	Fiscal Rev.Tax.Income	WEO_Oct2022
218	2021	Fiscal Rev.Tax.Income	WEO_Oct2022
196	2019	Debt Public debt	32	WEO_Oct2021
196	2019	Real GDP Growth	2	WEO_Oct2021
196	2020	Debt Public debt	44	WEO_Oct2021
196	2020	Real GDP Growth	-2	WEO_Oct2021
196	2021	Debt Public debt	52	WEO_Oct2021
196	2021	Real GDP Growth	5	WEO_Oct2021
199	2019	Debt Public debt	56	WEO_Oct2021
199	2019	Real GDP Growth	0	WEO_Oct2021
199	2020	Debt Public debt	69	WEO_Oct2021
199	2020	Real GDP Growth	-6	WEO_Oct2021
199	2021	Debt Public debt	69	WEO_Oct2021
199	2021	Real GDP Growth	5	WEO_Oct2021
213	2019	Debt Public debt	89	WEO_Oct2021
213	2019	Real GDP Growth	-2	WEO_Oct2021
213	2020	Debt Public debt	103	WEO_Oct2021
213	2020	Real GDP Growth	-10	WEO_Oct2021
213	2021	Debt Public debt		WEO_Oct2021
213	2021	Real GDP Growth	7	WEO_Oct2021
218	2019	Debt Public debt	59	WEO_Oct2021
218	2019	Real GDP Growth	2	WEO_Oct2021
218	2020	Debt Public debt	79	WEO_Oct2021
218	2020	Real GDP Growth	-9	WEO_Oct2021
218	2021	Debt Public debt	83	WEO_Oct2021
218	2021	Real GDP Growth	5	WEO_Oct2021
196	2019	Fiscal Rev.Tax.Income	18	WEO_Oct2021
196	2020	Fiscal Rev.Tax.Income	18	WEO_Oct2021
196	2021	Fiscal Rev.Tax.Income	18	WEO_Oct2021
199	2019	Fiscal Rev.Tax.Income	14	WEO_Oct2021
199	2020	Fiscal Rev.Tax.Income	13	WEO_Oct2021
199	2021	Fiscal Rev.Tax.Income	13	WEO_Oct2021
213	2019	Fiscal Rev.Tax.Income	5	WEO_Oct2021
213	2020	Fiscal Rev.Tax.Income	5	WEO_Oct2021
213	2021	Fiscal Rev.Tax.Income	5	WEO_Oct2021
218	2019	Fiscal Rev.Tax.Income	WEO_Oct2021
218	2020	Fiscal Rev.Tax.Income	WEO_Oct2021
218	2021	Fiscal Rev.Tax.Income	WEO_Oct2021
196	2019	Debt Public debt	32	WEO_Oct2020
196	2019	Real GDP Growth	2	WEO_Oct2020
196	2020	Debt Public debt	48	WEO_Oct2020
196	2020	Real GDP Growth	-6	WEO_Oct2020
196	2021	Debt Public debt	60	WEO_Oct2020
196	2021	Real GDP Growth	4	WEO_Oct2020
199	2019	Debt Public debt	62	WEO_Oct2020
199	2019	Real GDP Growth	0	WEO_Oct2020
199	2020	Debt Public debt	79	WEO_Oct2020
199	2020	Real GDP Growth	-8	WEO_Oct2020
199	2021	Debt Public debt	83	WEO_Oct2020
199	2021	Real GDP Growth	3	WEO_Oct2020
213	2019	Debt Public debt	90	WEO_Oct2020
213	2019	Real GDP Growth	-2	WEO_Oct2020
213	2020	Debt Public debt	97	WEO_Oct2020
213	2020	Real GDP Growth	-12	WEO_Oct2020
213	2021	Debt Public debt	87	WEO_Oct2020
213	2021	Real GDP Growth	5	WEO_Oct2020
218	2019	Debt Public debt	59	WEO_Oct2020
218	2019	Real GDP Growth	2	WEO_Oct2020
218	2020	Debt Public debt	69	WEO_Oct2020
218	2020	Real GDP Growth	-8	WEO_Oct2020
218	2021	Debt Public debt	68	WEO_Oct2020
218	2021	Real GDP Growth	6	WEO_Oct2020
196	2019	Fiscal Rev.Tax.Income	18	WEO_Oct2020
196	2020	Fiscal Rev.Tax.Income	18	WEO_Oct2020
196	2021	Fiscal Rev.Tax.Income	17	WEO_Oct2020
199	2019	Fiscal Rev.Tax.Income	15	WEO_Oct2020
199	2020	Fiscal Rev.Tax.Income	13	WEO_Oct2020
199	2021	Fiscal Rev.Tax.Income	13	WEO_Oct2020
213	2019	Fiscal Rev.Tax.Income	5	WEO_Oct2020
213	2020	Fiscal Rev.Tax.Income	5	WEO_Oct2020
213	2021	Fiscal Rev.Tax.Income	5	WEO_Oct2020
218	2019	Fiscal Rev.Tax.Income	20	WEO_Oct2020
218	2020	Fiscal Rev.Tax.Income	22	WEO_Oct2020
218	2021	Fiscal Rev.Tax.Income	31	WEO_Oct2020

Result if, for example:

User selection slicer 1: (linked to Variable)	Debt Public debt
User selection slicer 2: (linked to Vintage) First Vinstage	WEO_Oct2020
User selection slicer 3: (linked to Vintage) Second Vintage	WEO_Oct2022

the reported value below are, for variable "Debt Public debt", = second vintage: (WEO_Oct 2022) [minus] value first vintage: (WEO_Oct 2020).

Code	year	Variable	Value
196	2019	Debt Public debt	0
196	2020	Debt Public debt	-5
196	2021	Debt Public debt	-9
199	2019	Debt Public debt	-6
199	2020	Debt Public debt	7
199	2021	Debt Public debt	-14
213	2019	Debt Public debt	-2
213	2020	Debt Public debt	6
213	2021	Debt Public debt	-6
218	2019	Debt Public debt	0
218	2020	Debt Public debt	9
218	2021	Debt Public debt	12

[Sahir_Maharaj]

Hello @Javier_Arze,

Can you please try the following:

1. Ensure you have slicers set up for:

• Variable (linked to your "Variable" column)
• Vintage 1 (linked to your "Vintage" column)
• Vintage 2 (linked to your "Vintage" column)

2. Create a Measure for Value Difference

Value Difference = 
VAR SelectedVariable = SELECTEDVALUE('YourTable'[Variable])
VAR Vintage1 = SELECTEDVALUE('YourTable'[Vintage], "Vintage1Default")
VAR Vintage2 = SELECTEDVALUE('YourTable'[Vintage], "Vintage2Default")
VAR ValueAtVintage1 = 
    CALCULATE(
        SUM('YourTable'[Value]),
        'YourTable'[Variable] = SelectedVariable,
        'YourTable'[Vintage] = Vintage1
    )
VAR ValueAtVintage2 = 
    CALCULATE(
        SUM('YourTable'[Value]),
        'YourTable'[Variable] = SelectedVariable,
        'YourTable'[Vintage] = Vintage2
    )
RETURN ValueAtVintage2 - ValueAtVintage1

Hope this helps!

[Javier_Arze]

Thanks a lot Sahir_Maharaj.  I really apprecaite your help. I tried using the code you suggested but it did not work as desired. Initially I thought it could be realted to the fact that having two slicers linked to the same column: "vintage", may create a problem, so I copied the column vintage in the datasetm, and labeled the copy "vintage_2 ", then revised your code as follows:

Value Difference =

VAR SelectedVariable = SELECTEDVALUE('ToolsPan_WEO'[Variable])

VAR Vintage1 = SELECTEDVALUE('ToolsPan_WEO'[vintage], 0)

VAR Vintage2 = SELECTEDVALUE('ToolsPan_WEO'[vintage_2], 0)

VAR ValueAtVintage1 = CALCULATE(SUM('ToolsPan_WEO'[Value]),'ToolsPan_WEO'[Variable] = SelectedVariable, 'ToolsPan_WEO'[vintage] = Vintage1)

VAR ValueAtVintage2 = CALCULATE(SUM('ToolsPan_WEO'[Value]),'ToolsPan_WEO'[Variable] = SelectedVariable, 'ToolsPan_WEO'[vintage_2] = Vintage2)

RETURN

ValueAtVintage2-ValueAtVintage1

Yet this did not work eiter. I am reading about the use of Variables, on a DAX manual, and it states that in DAX, despite the name, any defined Variable is a constant. Once asigned a value the variable cannot be modified. Hence it seems to me that that is one of the reasons it would not work in this case, as the objective, is that the user can select one Variable and two Vintages from slicers in the page, and that the result table report the differences in value for the selected variable and two vintages. Other issue with the code you sugegsted is the SUM. There is nothing to be added in the dataset, because each row has a unique combination of counrty_code, variable, value, year, and vintage.  The result table should only pick two of these combinations for each country, reflecting two different vintages (selected by the user), and the variable selected, subtract those values, and return a table with the results for all the countries and years.     

[v-yiruan-msft · Accepted solution]

@Sahir_Maharaj Thanks for your contribution on this thread.

Hi @Javier_Arze ,

@Sahir_Maharaj  have provided a possible solution. Here you can make a little adjustment to get your expected result:

1. Create two dimension tables(Do not create any relationship with your fact table)

Vintage1 = VALUES('ToolsPan_WEO'[vintage])

Vintage2 = VALUES('ToolsPan_WEO'[vintage])

2. Create two slicers which apply the field from the above 2 dimension tables

3. Create a measure as below

NValue = 
VAR _vintage1 =
    SELECTEDVALUE ( 'Vintage1'[vintage] )
VAR _vintage2 =
    SELECTEDVALUE ( 'Vintage2'[vintage] )
VAR _value1 =
    CALCULATE (
        SUM ( 'ToolsPan_WEO'[Value] ),
        FILTER ( 'ToolsPan_WEO', 'ToolsPan_WEO'[vintage] = _vintage1 )
    )
VAR _value2 =
    CALCULATE (
        SUM ( 'ToolsPan_WEO'[Value] ),
        FILTER ( 'ToolsPan_WEO', 'ToolsPan_WEO'[vintage] = _vintage2 )
    )
RETURN
    _value2 - _value1

Best Regards