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.
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:
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?
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.
for your requirement, you could try to create a measure then use visual level filter to filter if not slicer show blank.
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.
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
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
here is my two pbix files, please try it.
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.
so there are some points that have to be considered:
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.
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.
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.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.