Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pasomaiya
New Member

Need to add columns to table visual based on slicer selection

I have a use-case wherein I am pulling a huge fact table from Azure Data Explorer and I need to create a table visual where there are few fixed columns in the visual and I need to provide a slicer with columns from the same fact table, this slicer will give a choice to the users to select columns and add that to the table visual on their choice.

 

I tried unpivoting the table but it is a large table and I am getting query limits exceeded message as I am using Azure Data Explorer as data source.

 

Can someone help me with this? I need to provide a slicer wherein users can select columns of their choice in table visual and the already selected columns by me doesnt get change, the new columns will just append to the existing table

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @pasomaiya ,

If cannot using unpivot feature due to the size of your data source, you can extract the column name as a new table like this(supposing there has been a column that you must show it in the visual, otherwise it could not be supported currently unless usng unpivot)

In my sample, supposing the index column must be shown in the visual, the query could be like this:

let
    Source = Table.ColumnNames(#"Table"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Column Name"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column Name] <> "Index"))
in
    #"Filtered Rows"

Create a measure like this:

A = 
SWITCH (
    SELECTEDVALUE ( 'Column Name'[Column Name] ),
    "Column1", MAX ( 'Table'[Column1] ),
    "Column2", MAX ( 'Table'[Column2] ),
    "Column3", MAX ( 'Table'[Column3] ),
    "Column4", MAX ( 'Table'[Column4] ),
    "Column5", MAX ( 'Table'[Column5] ),
    "Column6", SUM ( 'Table'[Column6] ),
    "Column7", SUM ( 'Table'[Column7] ),
    "Column8", SUM ( 'Table'[Column8] ),
    "Column9", SUM ( 'Table'[Column9] ),
    "Column10", SUM ( 'Table'[Column10] )
)

Use a Matrix visual not a table visual to show the result:

re.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

7 REPLIES 7
v-yingjl
Community Support
Community Support

Hi @pasomaiya ,

If cannot using unpivot feature due to the size of your data source, you can extract the column name as a new table like this(supposing there has been a column that you must show it in the visual, otherwise it could not be supported currently unless usng unpivot)

In my sample, supposing the index column must be shown in the visual, the query could be like this:

let
    Source = Table.ColumnNames(#"Table"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Column Name"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column Name] <> "Index"))
in
    #"Filtered Rows"

Create a measure like this:

A = 
SWITCH (
    SELECTEDVALUE ( 'Column Name'[Column Name] ),
    "Column1", MAX ( 'Table'[Column1] ),
    "Column2", MAX ( 'Table'[Column2] ),
    "Column3", MAX ( 'Table'[Column3] ),
    "Column4", MAX ( 'Table'[Column4] ),
    "Column5", MAX ( 'Table'[Column5] ),
    "Column6", SUM ( 'Table'[Column6] ),
    "Column7", SUM ( 'Table'[Column7] ),
    "Column8", SUM ( 'Table'[Column8] ),
    "Column9", SUM ( 'Table'[Column9] ),
    "Column10", SUM ( 'Table'[Column10] )
)

Use a Matrix visual not a table visual to show the result:

re.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

I am trying to implement your solution on a similar problemwhere I need to display data in a cost sheet based on Fiscal year(s) selected in the slicer. All attempts to implement have failed and I cannot use the UNPIVOT method as it makes the data unreadable.

Anonymous
Not applicable

What you can try is using bookmarks. If there are not too many scenarios, you can create a few table visuals, each containing the set of columns that you want them to choose from. Then create a new table that contains the column names (or combinations of columns). Add a button that will have the action to view the bookmark based on the selected value in the slicer. Not the most easiest way of doing this, but as your data is huge, this might work.

Thanks for the reply !

 

I have 50+ such columns which needs to be added in the slicer, so bookmarks will be very tedious 🙂

Anonymous
Not applicable

For the above link, they have very less data but for me its HUGE so Unpivot is not possible 🙂 I saw this link long back only

Anonymous
Not applicable

got it. Let me have a look

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors