Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

How to get the additional fields in table depending on variable ?

  • 1.  How to get the additional fields in table depending on variable ?

    Silver Contributor
    Posted Oct 27, 2021 06:22 PM

    Hi All ,

    I am transforming Qliksense App to Power Bi App.

    Can anyone please help how to approach - I have to create a variable -  vTMP_Max_Date

     

    MaxDate:
    Load Max(_Date) as _MaxDate
    Resident PH;

     

    LET vTMP_Max_Date = Num(Peek('_MaxDate', 0, 'MaxDate'));

     

    After creating the variable vTMP_Max_Date 

     

    I need to add the following field in the current table which is created by evaluating the variable vTMP_Max_Date 

     

    Join(PH)
    load distinct _Date,
    Interval($(vTMP_Max_Date) - _Date,'d') As PH.Last_n_days,
    if(Interval($(vTMP_Max_Date) - _Date,'d') =0,1,0) as PH.count_today,
    if(Interval($(vTMP_Max_Date) - _Date,'d') =7,1,0) as PH.count_7_days_ago
    resident PH
    ;

     

    Currently I have the PH table with below columns

    Powerr Bi.PNG

     

    I want to get the columns

    PH.Last_n_days,

    PH.count_today,
    PH.count_7_days_ago like below

     

    Like this.PNG



  • 2.  RE: How to get the additional fields in table depending on variable ?

    Posted 26 days ago

    Hi, 

    How do you set the variable value ( vTMP_Max_Date)?

    Do you mean it is the chosen date by the user? If so you need to use measures.

    If it is just the max date in the data, performance wise do it at import/data load script (sql/file???) i.e. in the source, if there are a lot of rows.

    It is not clear how you want to use it.



    ------------------------------
    /KR
    Lotta Carlsson
    BI Developer
    ------------------------------