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
mark_endicott
Helper II
Helper II

Using a Variable to filter CALCULATETABLE and SUMMARIZECOLUMNS

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
)
7 REPLIES 7
mark_endicott
Helper II
Helper II

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
Resident Rockstar
Resident Rockstar

@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

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

Parameters - Power Query | Microsoft Docs

@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... 

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.

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. 

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.