How to pass parameters based on filters and slicers

[harib]

Hi All,

We have a below query to use in Power BI. We need to give a date prompt to the user where they can select the required dates . 

However we dont have calendar filed in the query , if  we add calendar filed in the query  it is missing its granularity .

I would like to know can we pass parameters in the query level like paginated reports. 

select a11.ID, count(distinct a11.ACCOUNT_ID)
from FACT_Tbl a11
join Dim_Tbl a12
on (a11.ID = a12.ID)
join Date_Tbl a13
on (a11.DATE_ID = a13.DATE_ID)
where (
and a13.CALENDAR_DATE between Add_months(trunc(sysdate, 'MM'), -1) and trunc(sysdate, 'DD') - 0)
group by a11.ID

My report got stuck here. If anyone know can u please send sample file.

Thanks in advance.

[parry2k]

@harib Check this link, it will help Dynamic M query parameters in Power BI Desktop (preview) - Power BI | Microsoft Docs

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[harib]

@parry2k 

Thanks for the update.

I have followed the steps which they mentioned and declared the parameter in advance editor. however where de we need to give the variable at source level.Colud you pls check and help me.

let

    KustoParametersDeclareQuery = Text.Combine({"declare query_parameters(",

                                 "startTimep:datetime = datetime(", DateTime.ToText(StartTimeMParameter, "yyyy-MM-dd hh:mm"), "), "    }),

    finalQuery = ({KustoParametersDeclareQuery}),

    Source = Oracle.Database("BDWDEV", [HierarchicalNavigation=true]),

    APP_PBI_RPT = Source{[Schema="APP_PBI_RPT"]}[Data],

    VW_TBQL4G97AMD1 = APP_PBI_RPT{[Name="VW_TBQL4G97AMD002"]}[Data]

in

    VW_TBQL4G97AMD1

Thanks in advance

[parry2k]

@harib first why you are pre-aggregating the data, you should bring the data to a granular level and then use measures in power to aggregation and make the calculation and that will take care of the problem.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[harib]

@parry2k 

I do agree, but the thing is we need to use that query directly in Power BI. Actuvally it's temp table like that we have so many queries in one report. 

Is it possible to pass parameters in the query level.  😞