Slicer value to hit back the DB2 StoredProcedure and sliced value should be populated as dataset

[Sri123nath]

Hi Everyone,

Please find my below requirement and it will be of great help if some one can provide an idea on how to execute  it.

Datasource : DB2 Database

Query to be used for Data Visualization : DB2 Stored procedures which will return around 3 to 5 million records as result

Report Design : 5 to 10 slicers at the top and based on selection of Slicer , the table visual to be populated in the same page of the report.

End user to select the 5 to 10 slicers and based on the selection , slicer values combinations should filter against the DB2 Stored procedure records ( i.e lets say total stored procedure record is 3 million records and  lets assume the selection of slicer value is  500 records. this 500 records will be filtered against the 3 million records and that should be the dataset for the report). 

Stored procedure in this scenario is Import Mode ( as Direct query not supporting for my ODBC).

Is there any work around to achieve this requirement.

[v-jayw-msft · Accepted solution]

Hi @Sri123nath ,

You could use values() function to catch the values in slicers.

VALUES function (DAX) - DAX | Microsoft Learn

Create a measure like:

measure = IF(selectedvalue([column1]) in values([slicer1])&&selectedvalue([column2]) in values([slicer2])&&selectedvalue([column3]) in values([slicer3])&&...etc,1,0)

Then add this measure to visual filter and set value = 1.

Best Regards,

Jay

[Sri123nath]

Many Thanks, Will try to incorporate this solution