create a table from a measure

[Anonymous]

Hello,

I need some help please,

- I have a table like this one:

- I created a slicer:

- Now I need to create a table to import in power query that show only the value filtered in the slicer.

Do you know how to do it?

Thank you in avance.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hello, the problem is that in the first table, data are coming form a web API, and I think it's not possible to do a direct query with an API.

Thank you

[rohit1991]

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.

[Anonymous]

Thank you for your answer.

Yes, my purpose to use it in power query not just in a visual.

I already created a parameter in power query but I don't know how to do to this step you mentionned:

Manually link it to a slicer selection using a disconnected table and DAX logic.

Could you please explean how to do it?

Thank you

[danextian]

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.