Forum Discussion
Filter SQL statement
hi, Raul
Slicer in report is filter for data model, and you need to use Parameters in Power Query
here is a blog for you refer to:
https://www.mssqltips.com/sqlservertip/4475/using-parameters-in-power-bi/
Best Regards,
Lin
Thanks v-lili6-msft for your post, but I think this is not de best option for me because I don't want to specify any parameter. I want select a date period on the slicer and then, applied this period to filter de SQL sentence.
I think that the best option is change the SQL statement. I need the TotalCoste (731,53) and the TotalFacturacio (2.913,18) for a Client (BAST), Areas (LAB) and date period (01/01/2018-30/11/2018) specified with the filters (slicers) of the report to create a calculate column: Rep = (Coste * TotalFacturacio)/ TotalCoste (TotalFacturacio is not specified on the SQL query exemple)
I've try with a measure inside the Power BI file but for performance is not possible (when I don't applied any filter, the report hangs). That's why I do it inside the SQL query.
Anyone have an optimized query?
hi, Raul
I know what you want but in power bi, SQL statement is used to get data for the data source,
but slicer can only be used to filter the data model in the report, also calculate column or calculate table can't be affected by any slicer.
slicer only can affect calculate measure.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
for your requirement, you could try to create a measure then use visual level filter to filter if not slicer show blank.
Best Regards,
Lin
Hi v-lili6-msft and thanks.
When you say "then use visual level filter to filter if not slicer show blank", what do you means? I don't understand you.
Regards.
hi, Raul
For example:
Step1:
Create a fact table for the field you would be dragged into slicer
In my sample pbix, I would use Period
then create a relationship between them
Step2:
You could try to use SELECTEDVALUE or ISFILTERED to create some simple measure like below:
Measure 11 = IF(ISFILTERED(InvoiceDetail[Period])=TRUE(),1,2) Measure 12 = IF(ISBLANK(SELECTEDVALUE(InvoiceDetail[Period]))=TRUE(),2,1)
Then drag measure into visual level filter
eg. I drag Measure 11 into visual level filter and set filter is 1
Also, you could copy a Period column
and use these two visual
Measure 11 = IF(ISFILTERED(InvoiceDetail[Period filter]),1) Measure 12 = IF(ISBLANK(SELECTEDVALUE('InvoiceDetail'[Period filter]))=FALSE(),1)then drag the copy field into slicer
https://docs.microsoft.com/en-us/power-bi/power-bi-report-add-filter
here is my two pbix files, please try it.
https://www.dropbox.com/s/dfwi75ksjffmo91/test4.pbix?dl=0
https://www.dropbox.com/s/yahgvm0vp1mk7k0/test5.pbix?dl=0
Best Regards,
Lin