Filter SQL statement

[Raul]

Hello everybody,

I have this source from SQL query for a Power BI file:

WITH TotalCostes1 AS (SELECT SUM(Coste) AS TCoste1, [Cod_ cliente], Areas
FROM dbo.AuxFact AS F1
WHERE (CodConcepto = 'FFGO' AND ([Fecha registro] Between '20180101' and '20181031') OR
([Fecha registro] Between '20180101' and '20181031') AND (Tipo = 'Abono')
GROUP BY [Cod_ cliente], Areas), 
TotalCostes2 AS (SELECT SUM(Coste) AS TCoste2, [Cod_ cliente]
FROM dbo.AuxFact AS F2
WHERE ([Fecha registro] Between '20180101' and '20181031') AND (Areas = 'FIS')
GROUP BY [Cod_ cliente])

SELECT dbo.AuxFact.Colaborador, dbo.AuxFact.Horas, dbo.AuxFact.Coste, dbo.AuxFact.[Fecha registro], dbo.AuxFact.Areas, 
dbo.AuxFact.Delegacion, dbo.AuxFact.[Cod_ cliente], CAST(dbo.AuxFact.Facturacion AS FLOAT) AS Facturacion,
CASE WHEN AuxFact.Areas = 'FIS' THEN TCoste1 - ISNULL(TCoste2, 0) ELSE TCoste1 END AS TmpCost 
FROM dbo.AuxFact LEFT OUTER JOIN
TotalCostes2 AS TC2 ON dbo.AuxFact.[Cod_ cliente] = TC2.[Cod_ cliente] LEFT OUTER JOIN
TotalCostes1 AS TC1 ON dbo.AuxFact.[Cod_ cliente] = TC1.[Cod_ cliente] AND dbo.AuxFact.Areas = TC1.Areas

The SQL statement has a fixed date filter (Between '20180101' and '20181031') that I want  to be variable selected from the Power BI report filter:

How can I do that the date filter selected in the Power BI Report applied in the SQL statement to return tha values for this period? 

Thank you very much.

[v-lili6-msft]

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

[Raul]

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?

[v-lili6-msft]

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

[Raul]

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.

[v-lili6-msft]

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

[Raul]

Hello @v-lily6-msft,
Thanks alot for your complete explanation and example, it's very clarefull and understandable, but don't resolve my problem with the date filter or the measures.
Has anyone another suggestion? Thank you.

[TomMartens]

Hey @Raul,

so there are some points that have to be considered:

• the connection mode "direct query" allows to interactively query the data from SQL Server but does not allow to create calculated columns, just measures
• the connection mode "import" allows "calculated columns", but the number of rows is fixed until the next data refresh

From my point of view, the greatest flexibility is gained by importing the data into Power BI or use Analysis Services Tabular (on-premises or with Azure) if the size of the data model exceeds Power BI Desktop or the size available for datasets in Power BI Service.

What is the current number of rows / columns, and what is your expected growth for the next years?

Maybe if you provide some sample data, we will be able to provide some DAX statements, that will overcome the performance issues you are facing.

Thanks,

Tom

[Raul]

Hello @TomMartens,

This is and example of my Power BI file. The number of total records in this moment are about 320.000 and it's grow about 100.000 by year. The number of columns is about 15.

PWBI File

I don't know if the formulas of the TempCOST, TempFACT and Distribution measures are the best option. On the final table visualization the TempCOST and the TempFACT measures not shown.

Thanks for your help.