How to make Dynamic Key column that changes to formula based on user selection?

[sbellmore]

I have two Tables These are simplified... 

The table with Formulas (this table is Called Table1)

Custom     Custom.1

Option 1	[Source System]&[FY]&[BN]&[BS]&[Fnd]&[Pg]
Option 2	[Source System]&[FY]&[BN]&[BS]&[Fnd]&[Pgr]&[AP]]

and the table I am trying to make the Dynamic Key Column in: (Table2)

Source System	SOF	Transaction FY	FY	BN	BS	Fnd	Pg	AFP	Allotment	Commitments	Obligations	Expensed	Disbursements	AP	OA	MDEP	CI
System1	D	2024	24	4012	1	40120D4	BASE	1234	1234	1234	1234	1234	1234	105678	6A	VHMN	43006
System2	D	2024	24	2025	1	20250D4	BASE	1234	1234	1234	1234	1234	1234	1216348	6A	TACS	24767
System3	D	2024	24	1205	1	12050024	BASE	1234	1234	1234	1234	1234	1234	13128209	6A	LOMG	A567J
ETC	D	2024	24	2025	1	20251024	BASE	1234	1234	1234	1234	1234	1234	11134204	6A	PERN	7457

2.  This is the first formula to know the user selection Option 1:

SelectedFormula = SELECTEDVALUE('Table1'[Custom])  - when i view this in a table I verify that Option 1 is selected. 

and the second formula. 

ConcatenatedKey =
SWITCH (
SELECTEDVALUE('Table'[Custom]),
"Option 1", CONCATENATEX(SUMMARIZE('Table2', [Source System], [FY], [BN], [Fnd], [Pg]), [Source System] & [FY] & [BN] & [Fnd]& [Pg], ", "),
"Option 2", CONCATENATEX(SUMMARIZE('Tablel2', [Source System], [FY], [BN], [Fnd], [Pg], [AP]), [Source System] & [FY] & [BN] & [Fnd] & [Pg] & [AP], ", "),
BLANK()
)

UPDATE^: This works as a measure but I Cant put this in a table....  the end goal is to have a dynamic key in both tables to do a merge off of, this will allow the user to choose different level of detailed analysis by selecting how to define the key. 

ConcatenatedKey =

SWITCH (

    [SelectedFormula],

    "Option1", CONCATENATEX('Table2', [Source System] & [FY] & [BN] & [Fnd] & [Pg]),

    "Option2", CONCATENATEX('Table2', [Source System] & [FY] & [BN] & [Fnd] & [Pg] & [AP]),

    BLANK()

)

But When I assign the Concatenated Key to a column in Table2, it returns no value. Its blank for every row. As I said above, trying to find a way to provide the user to select different levels of detailed analysis. If i need a different approach please let me know! 

The value held by Selected formula  when Option 1 is selected is Option 1

any help would be appreciated

[v-yifanw-msft]

Hi @sbellmore ,

You are on the right track in terms of usage and functionality based on the information you provide.Metrics in Power BI change dynamically based on slicer selections. Metrics are recalculated based on slicer selections and can reflect current user selections.

You can add Custom to the slicer and select Option to make the measure change automatically as needed.

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[sbellmore]

Hey! 

Thanks for the response, it works when you add the metric to a visual. But I guess I need something in Power Query to do the same thing... In Table view on Power BI, If i Go back to Table2, and add column and add column = Concatenated Key  -> the result is blank for the entire column. Thats where my problem is. I would like to add it as a column to each table and then do an inner join off that column to get a predifned table with different levels of analysis. not sure if I am asking too much. I very well may be, but I am just trying to teach myself a way to do this. If it is even possible. Thank you So much!