Date filter selections based on Measure

[SK28]

Hi Everyone,

I have a requirement, Can you please help me with the same,

I have a data as shown below here, 

Below is the pivot table, I have created, Cost as a category and year with the sum of data, the requirement here is  based on the selection in single measure only has to be filtered

Dimension1	Dim2	Capex Cost					Running Cost					Operation Cost
		2024	2025	2026	2027	2028	2024	2025	2026	2027	2028	2024	2025	2026	2027	2028
abc	bcd	0	12	23	34	0	0	0	0	0	0	5	17	28	39	50
ZXC	cve	0	13	24	35	0	0	0	0	0	0	5	18	29	40	51
wes	qws	0	14	25	36	0	0	0	0	0	0	5	19	30	41	52
tvs	xsz	0	15	26	37	0	0	0	0	0	0	5	20	31	42	53
tre	dfv	0	16	27	38	0	0	0	0	0	0	5	21	32	43	54

For example if a user has selected 2024 in Capex cost only 2024 for capex has to be filtered and remaining should be as is the output should be as shown below

Dimension1	Dim2	Capex Cost	Running Cost					Operation Cost
		2024	2024	2025	2026	2027	2028	2024	2025	2026	2027	2028
abc	bcd	0	0	0	0	0	0	5	17	28	39	50
ZXC	cve	0	0	0	0	0	0	5	18	29	40	51
wes	qws	0	0	0	0	0	0	5	19	30	41	52
tvs	xsz	0	0	0	0	0	0	5	20	31	42	53
tre	dfv	0	0	0	0	0	0	5	21	32	43	54

if a user has selected 2024 in Capex cost, 2024,2025,2026 in running cost the output should be as shown below

Dimension1	Dim2	Capex Cost	Running Cost			Operation Cost
		2024	2024	2025	2026	2024	2025	2026	2027	2028
abc	bcd	0	0	0	0	5	17	28	39	50
ZXC	cve	0	0	0	0	5	18	29	40	51
wes	qws	0	0	0	0	5	19	30	41	52
tvs	xsz	0	0	0	0	5	20	31	42	53
tre	dfv	0	0	0	0	5	21	32	43	54

if a user has selected 2024,2028 in Capex cost, 2024,2025,2026 in Running cost the output should be as shown  and 2027,2028 in Operation cost the output should be as shown below

Dimension1	Dim2	Capex Cost		Running Cost			Operation Cost
		2024	2028	2024	2025	2026	2027	2028
abc	bcd	0	0	0	0	0	39	50
ZXC	cve	0	0	0	0	0	40	51
wes	qws	0	0	0	0	0	41	52
tvs	xsz	0	0	0	0	0	42	53
tre	dfv	0	0	0	0	0	43	54

based on the selection in that particular year the data has to be filtered for that particular category,

can you please help me with this requirement, Appreciate your help

thanks in advance, Even alternate states works

[JandreKillianRIC]

Hi @SK28 

I think the problem you are facing is you cant select a measure. Only fields / dimensions. 

However this can be achieved using a variable input ( using the buttons) to show / hide a table. IE Capex / Running cost. And then selecting the dimension. I would recommend using the new layout container to use the same space on the sheet to achieve this. 

Regards - Jandre

[SK28]

Hi @JandreKillianRIC thanks for the response 

I meant the Filter of Year column if I select based on that selection only that particular cost has to be shown and remaining should be unaffected 

[SK28 · Accepted solution]

Solution:

create three alternate states
in dimension : 

=
if(dim_type_cost='capex' ,
aggr(concat({[capex]} distinct [capex Year],','),[capex Year],dim_type_cost)
,
if(dim_type_cost='Running Cost' ,
aggr(concat({[RunningCost]} distinct [Running Cost Year],','),[Running Cost Year],dim_type_cost)
,
if(dim_type_cost='Operational Cost' ,
aggr(concat({[OperationalCost]} distinct [Operational Cost Year],','),[Operational Cost Year],dim_type_cost))))

measure:

Pick(match(dim_type_cost,'capex' ,'Running Cost' ,'Operational Cost'),
Num(Round(sum({[capex]<dim_type_cost={'capex'},[H P Name]=$::[H P Name]>}Measure)))
, Num(Round(sum({[RunningCost]<dim_type_cost={'RunningCost'},[H P Name]=$::[H P Name]>}Measure)))/vmillions
,Num(Round(sum({[OperationalCost]<dim_type_cost={'OperationalCost'},[H P Name]=$::[H P Name]>}Measure)))/vmillions
)