Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to assign default values for selection parameters from a query

Hi Experts,

    I'd like to have power BI to refresh report data from a query based on a predefined criteria ,

    e.g, get the latest six months' data from a data source (query), with a scheduled job in power BI service.

    In case above, we need to select month values specifically to import data from a query 

    to form the dataset for working on reports and dashboards in power desktop,

    then publish them to power BI service cloud.

    The problem is that selecting the last six month values and

    publishing periodically seems not make any sense, not really a good idea.

    Therefore, is there any smart way to have this job done automatically ?

 

    In the "Advanced Editor" under query editor in power BI desktop, I can see the month values there like below

.....

    {Cube.ApplyParameter, "[!V000001]", {{"[0CALMONTH].[202402]", "[0CALMONTH].[202403]", "[0CALMONTH].[202404]", "[0CALMONTH].[202405]", "[0CALMONTH].[202406]", "[0CALMONTH].[202407]"}}},

   ....

     My idea is to have those values determined dynamically, e.g, those values would becomes [202403], [202404]....[202408]

     when the scheduled job runs in August. 

     Not sure whether it is possible or how to make the code changes to the codes in  "Advanced Editor"

     to make such a dynamic value determination happen.

 

    Any advice will be greatly apprecated.

 

     

    

 

Need Help
  • dk_dk's avatar
    dk_dk
    2 years ago

    I have moved your post to the Power Query section of the forum, hopefully someone here will be more knowledgeable and can provide you with a more elegant solution ๐Ÿ™‚

3 Replies

  • dk_dk's avatar
    dk_dk
    Super User
    2 years ago

    Hi Anonymous 

    I am not a power query expert, but hopefully this solution works for you. I am sure someone can make the M code a bit more elegant:

    My sample query with hardcoded values

    let
    Source = Table.FromRows(
    {
        {202407},
        {202406},
        {202405},
        {202404},
        {202403},
        {202402}
    })

in
    Source

    This just creates a table that contains the last 6 months in the format you want, hardcoded.

    The modified query to return the same result dynamically:

    let
    refTime = DateTime.Date(DateTime.LocalNow()),
    prevmonth5 = Date.AddMonths(refTime,-5),
    prevmonth4 = Date.AddMonths(refTime,-4),
    prevmonth3 = Date.AddMonths(refTime,-3),
    prevmonth2 = Date.AddMonths(refTime,-2),
    prevmonth1 = Date.AddMonths(refTime,-1),




    Source = Table.FromRows(
    {
        {Date.ToText(refTime,"yyyyMM")},
        {Date.ToText(prevmonth1,"yyyyMM")},
        {Date.ToText(prevmonth2,"yyyyMM")},
        {Date.ToText(prevmonth3,"yyyyMM")},
        {Date.ToText(prevmonth4,"yyyyMM")},
        {Date.ToText(prevmonth5,"yyyyMM")}
    })
in
    Source


    I am not sure exactly how, but there should be a way to incorporate these variables (the refTime and prevmonths) in your query, in place of the hardcoded values. The query will calculate the current time with every refresh, so the months should change dynamically.


    Hope this helps!

    • dk_dk's avatar
      dk_dk
      Super User
      2 years ago

      I have moved your post to the Power Query section of the forum, hopefully someone here will be more knowledgeable and can provide you with a more elegant solution ๐Ÿ™‚

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi dk_dk,

            Thank you for your solution, it's really helpful.

            It works as I thought, the codes I made as below.

           ..............

           /* To define ym variables to get latest 6 months dynamically */
        CurrDate = DateTime.Date(DateTime.LocalNow()),
        prevmonth5 = Date.AddMonths(CurrDate,-5),
        prevmonth4 = Date.AddMonths(CurrDate,-4),
        prevmonth3 = Date.AddMonths(CurrDate,-3),
        prevmonth2 = Date.AddMonths(CurrDate,-2),
        prevmonth1 = Date.AddMonths(CurrDate,-1),
        // Year yyyy
        Y5 = Number.ToText(Date.Year(prevmonth5)),
        Y4 = Number.ToText(Date.Year(prevmonth4)),
        Y3 = Number.ToText(Date.Year(prevmonth3)),
        Y2 = Number.ToText(Date.Year(prevmonth2)),
        Y1 = Number.ToText(Date.Year(prevmonth1)),
        Y0 = Number.ToText(Date.Year(CurrDate)),
        // Month mm
        M5 = Number.ToText(Date.Month(prevmonth5),"00"),
        M4 = Number.ToText(Date.Month(prevmonth4),"00"),
        M3 = Number.ToText(Date.Month(prevmonth3),"00"),
        M2 = Number.ToText(Date.Month(prevmonth2),"00"),
        M1 = Number.ToText(Date.Month(prevmonth1),"00"),
        M0 = Number.ToText(Date.Month(CurrDate),"00"),
        // YearMomth - yyyymm
        YM5 = Text.Combine({"[0CALMONTH].[", Y5, M5, "]"}),
        YM4 = Text.Combine({"[0CALMONTH].[", Y4, M4, "]"}),
        YM3 = Text.Combine({"[0CALMONTH].[", Y3, M3, "]"}),
        YM2 = Text.Combine({"[0CALMONTH].[", Y2, M2, "]"}),
        YM1 = Text.Combine({"[0CALMONTH].[", Y1, M1, "]"}),
        YM0 = Text.Combine({"[0CALMONTH].[", Y0, M0, "]"}),
        #"QM_C03/ZQ_QM_C03_001X" = QM_C03{[Id="QM_C03/ZQ_QM_C03_001X"]}[Data],
        #"Added Items" = Cube.Transform(#"QM_C03/ZQ_QM_C03_001X",
        {
        // {Cube.ApplyParameter, "[!V000001]", {{"[0CALMONTH].[202402]", "[0CALMONTH].[202403]", "[0CALMONTH].[202404]", "[0CALMONTH].[202405]", "[0CALMONTH].[202406]", " [0CALMONTH].[202407]"}}},
        {Cube.ApplyParameter, "[!V000001]", {{YM5, YM4, YM3, YM2, YM1, YM0}}},

        ...........................