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

[mscla]

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/... but in my case a different solution is needed

[mscla · Accepted solution]

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

[Anonymous]

Hi @mscla 

Thank you for sharing!

Regards,

Nono Chen

[Anonymous]

Hi @mscla 

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

Regards,

Nono Chen

[Anonymous]

Hi @mscla 

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.

[mscla]

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

[Anonymous]

Hi @mscla 

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.

[foodd]

Hello @mscla , 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-...

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/1447...

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

[mscla]

Hi @foodd 

this is my working progress file 

Sales_parameter_filter.pbix