topic Re: create a table that contains the columns chosen by the user in the slicer in DAX Commands and Tips

create a table that contains the columns chosen by the user in the slicer

Anonymous — Mon, 08 Jul 2024 09:52:14 GMT

Hi!
Is it possible create a summarize table that bring a column selected by user?

My main table has 6 columns that indicate the possible priority of lots in which each sale (key) may belong to certain vendors.

KEY	JOHN_ANA_PETER	JOHN_PETER_ANA	PETER_ANA_JOHN	PETER_JOHN_ANA	ANA_PETER_JOHN	ANA_JOHN_PETER
10255434	ANA	ANA	ANA	ANA
10256107	ANA	ANA	ANA	ANA	ANA	ANA
10260048	JOHN	JOHN			ANA	JOHN
10258289	ANA	ANA			ANA	ANA
10253959	PETER	PETER	PETER	PETER	PETER	PETER
10256459	ANA	ANA	ANA	ANA	ANA	ANA

I created a Field parameter in power bi that allows the user to choose which of the 6 columns he wants as batch priority

Parameter	Parameter Fields	Parameter Order
JOHN, ANA, PETER	table[JOHN_ANA_PETER]	0
JOHN, PETER, ANA	table[JOHN_PETER_ANA]	1
PETER, ANA, JOHN	table[PETER_ANA_JOHN]	2
PETER, JOHN, ANA	table[PETER_JOHN_ANA]	3
ANA, PETER, JOHN	table[ANA_PETER_JOHN]	4
ANA, JOHN, PETER	table[ANA_JOHN_PETER]	5

Now, how to create a table filtered by slicer?

For example, if the user selects "JOHN, ANA, PETER" the table should only have the columns KEY and JOHN, ANA, PETER.

I've tried this

Filtered_Table = VAR slicerselected = SELECTEDVALUE(Parameter[Parameter]) RETURN FILTER( Table, SWITCH( slicerselected, 0, Table[JOHN_ANA_PETER], 1, Table[JOHN_PETER_ANA], 2, Table[PETER_ANA_JOHN], 3, Table[PETER_JOHN_ANA], 4, Table[ANA_PETER_JOHN], 5, Table[ANA_JOHN_PETER] ) )

but show this: "Column [Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression."

I found posts like this https://community.fabric.microsoft.com/t5/Desktop/SUMMARIZE-Switch-Columns-using-a-Parameter-Filter/m-p/2850326#M982428, but in my case a different solution is needed

Re: create a table that contains the columns chosen by the user in the slicer

foodd — Mon, 08 Jul 2024 09:54:32 GMT

Hello Anonymous , and thank you for sharing a question with the Community. The following is informational. Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Re: create a table that contains the columns chosen by the user in the slicer

Anonymous — Mon, 08 Jul 2024 10:15:15 GMT

Hi foodd

this is my working progress file

Sales_parameter_filter.pbix

Re: create a table that contains the columns chosen by the user in the slicer

Anonymous — Tue, 30 Jul 2024 06:38:41 GMT

Hi Anonymous

A late reply.

In fact, you only need to put the parameter into the table visual to enable you to display the data by selecting the parameter.

Here is the result.

Regards,

Nono Chen

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

Re: create a table that contains the columns chosen by the user in the slicer

Anonymous — Tue, 30 Jul 2024 06:55:27 GMT

Hi Anonymous

thanks for reply...

Actually I need to create a DAX table, not a visual, but I think it is not possible! DAX dont allow to use parameter or measures based on slicers to filter a DAX table...

bye

Re: create a table that contains the columns chosen by the user in the slicer

Anonymous — Tue, 30 Jul 2024 07:15:43 GMT

Hi Anonymous

Your idea is right.

The role of the parameter is to:

Using parameters can be used to dynamically adjust data source properties, replace values, and filter data rows.

For example, you can create a parameter to select different date ranges or geographic regions to dynamically adjust the data in the report.

Or with parameters, you can create interactive slicers or sliders in the report that allow report users to tailor the view of the data to their needs.

Regards,

Nono Chen

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

Re: create a table that contains the columns chosen by the user in the slicer

Anonymous — Thu, 01 Aug 2024 05:44:55 GMT

Hi Anonymous

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

Re: create a table that contains the columns chosen by the user in the slicer

Anonymous — Thu, 01 Aug 2024 06:24:47 GMT

Hi Anonymous

I solved it by using Power Automate and a stored procedure to record the data into SQL server and then write it back with a direct query connection

thanks

Re: create a table that contains the columns chosen by the user in the slicer

Anonymous — Thu, 01 Aug 2024 07:03:13 GMT

Hi Anonymous

Thank you for sharing!

Regards,

Nono Chen