Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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:
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.
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:
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.
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 🙂
Maybe this post will help you:
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
got it. Let me have a look