Using a Variable to filter CALCULATETABLE and SUMMARIZECOLUMNS

[mark_endicott]

Hi,

Does anyone know why I cannot pass a variable using SELECTEDVALUE into the filter of CALCULATETABLE? The DAX I'm using is below, if I remove the variable and hard code a filter into "filter_list[filter_name] = " the table builds as expected, the the below  doesnt produce any data in the table when I select a value from [filter_name]

Uploader_filter_table =

VAR S_value =

SELECTEDVALUE( filter_list[filter_name] )

RETURN
CALCULATETABLE( 

          SUMMARIZECOLUMNS( filter_list[ISBN] ), 

                                    filter_list[filter_name] = S_value

)

[mark_endicott]

Thanks @Daryl-Lynch-Bzy, I was trying to create a table, had assumed the table was created after data load when using DAX. My model uses RLS and is from an Oracle datasource, so as far as I'm aware this means I am unable to use a Power Query Parameter. 

[Daryl-Lynch-Bzy]

@mark_endicott 

Hi Mark, it looks like you are creating a Table rather than a Measure.  You cannot use Slicer Selection when creating Tables because the Table is created at the point of data load.  The Slicer selection is made after this point when rendering the visuals.  You may need to consider switching to Power Query parameter setting, or some form outside of Power BI.

If you intention is to create a Measure, the result need to be a Scalar value not a Table.

I hope this helps.
Daryl

[Daryl-Lynch-Bzy]

The Oracle data connector should support parameters,  Give it try.

Parameters - Power Query | Microsoft Docs

[mark_endicott]

@Daryl-Lynch-Bzy - not what the MS documentation suggests, but we are also using Row Level Security and the MS documentation indicates this will not work either:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considerat... 

[Daryl-Lynch-Bzy]

Have you consider the following:

Connect to an Oracle database with Power BI Desktop - Power BI | Microsoft Docs

Overview of single sign-on (SSO) for on-premises data gateways - Power BI | Microsoft Docs

[Daryl-Lynch-Bzy]

Sorry @mark_endicott, I think we are confusing Direct Query and Dynamic M Parameters documentation.  When you connect to an Oracle Database - direct query is available.  In this scenario the slicer selections would be included in the Query sent to the Oracle DB (as the data is not imported). 

The scenario you are referring is when Direct Query is not available but default (hence is suggest that SQL is not supported).  This new feature allows you to bind parameters from the slicer into the M script required to pull the data from an API for example.  I have not personally tried this approach though.

[mark_endicott]

Right ok, I see where my confusion was. The reason I was trying to create a Summarized DAX table filtered on a value from a slicer (from a Direct Query table) was because the DQ table will have duplication in a relationship column that the slicer removes - hence I could remove a many to one join that needs to go in both directions as the summarized table would make this one to one. 
I dont think a Power Query Parameter will help here. 

Making the slicer on the Direct Query table single select effectively makes the relationship one to one in any case, we were probably just trying to over engineer it in the name of perfection.