Forum Discussion

Raul's avatar
Raul
Post Patron
7 years ago

Filter SQL statement

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.

 

Need Help

8 Replies

Replies have been turned off for this discussion
    • Raul's avatar
      Raul
      Post Patron
      7 years ago

      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's avatar
        v-lili6-msft
        Community Support
        7 years ago

        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