create a table from a measure

Hi Anonymous 

 

That is not possible and I dont think it ever will. While you can use a measure in a calculated table, calculated tables are unaware of any slicer selection. Also what happens in the model does not affect the query so you cannot pass the selected value in a slicer to the query editor.

hi Anonymous 

Power BI does not allow dynamically creating a physical table from a slicer selection in Power Query, but you can create a virtual table using DAX and display it in a visual.

1. Create a Measure to Capture Slicer Selection

Use a measure to capture the selected stock symbol:

Selected_Ticker = SELECTEDVALUE('StockSymbol Sheet'[StockSymbol])

 

2. Create a Table Visual with Filtered Data: To display only the filtered selection in a table visual, use:

Filtered_Stocks =
FILTER('StockSymbol Sheet', 'StockSymbol Sheet'[StockSymbol] = [Selected_Ticker])

 

3. If You Need a Physical Table in Power Query (Workaround)

Power Query does not dynamically filter based on a slicer, but you can:

1. Create a Parameter in Power Query.
2. Manually link it to a slicer selection using a disconnected table and DAX logic.
3. Refresh Power Query to reflect changes.

Hi Anonymous ,
Thanks for rohit1991 reply.

The following is an example of how to implement the dynamic use of parameters in a power query in power bi desktop.

First your initial table needs to be a Dierct Query mode table (Here is a table used that originated in sql)

Then go to power query and create a parameter

" " meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

Returns the power bi desktop and creates a table with the same data as the original table.

Go to the model view and bind the fields of the new table to the parameters in the power query.

Go to power query and use the following code in the initial table

let
    selectedStockSymbol = if Type.Is(Value.Type(Parameter_PQ), List.Type) then 
      Text.Combine({"'", Text.Combine(Parameter_PQ, "','") , "'"})
    else
      Text.Combine({"'" , Parameter_PQ, "'"}),

    selectAllStockSymbol = if Type.Is(Value.Type(Parameter_PQ), List.Type) then 
      List.Contains(Parameter_PQ, "__SelectAll__")
    else
      false,
      
    query = "
      SELECT * FROM YourTableName",
      
    dynamicFilter = if selectAllStockSymbol then ""
                    else Text.Combine({"WHERE [StockSymbol Sheet] IN (", selectedStockSymbol, ")"}),

    finalQuery = if dynamicFilter = "" then query else Text.Combine({query, " ", dynamicFilter}),
    
    Source = Sql.Database("YourSqlServerName", "YourDataBaseName", [Query = finalQuery])
in
    Source

Create a slicer

Because of the direct query mode, you can't view the table data in a table view, but you can display the table data in a table visualization

Final output

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

 

Best regards,
Albert He

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

 

 

 

 

 

Hi Anonymous ,
According to your description, your data source source WEB API, then your data is stored in import mode. In this case, it is not possible to implement the option to generate tables using slicers. Also the table generated using dax cannot be used in power query. If you want to generate a table in power query, the direct way is to create the table using the parameter or apply the slicer option as in m code.

Best regards,
Albert He

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