Forum Discussion

miss_stoat's avatar
miss_stoat
Frequent Visitor
3 years ago

Dynamic Parameters in PBI Report Builder using a published dataset

Hi

I am moving over from a very old SSRS Report builder to PBI Report Builder. I have my published datasets and I am (mostly) OK with creating parameters but I am stuck on a particular type of parameter.

 

In SSRS I could create a parameter using a case statement in the where clause of the SQL query, for example:

 

where case when @ConsolidateBy = 'Job No' then f.job_no
when @ConsolidateBy = 'HAWB' then ndi.con_note_number
else f.invoice_no end in (@Reference)

 

So, if a user selected 'Job No' in the 'Consolidate By' Parameter, and entered a valid Job No in the 'Reference' Parameter it would give results. I cannot figure out how to do this with a published dataset...and it's driving me barmy! How can I do this with a published PBI Dataset? Can it be done? as it stands I am looking at creating three reports (one for Job No, one for HAWB and one for Invoice No) where I currently have one...

 

I'd be grateful for any tips...

Need Help

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    3 years ago

    Hi miss_stoat ,

     

    We can use parameters in the DAX query of the dataset to return data dynamically, like this.

     

    // DAX Query 

DEFINE 
  VAR __DS0FilterTable =  
    TREATAS({@Parameter1}, 'Table'[col1]) 
  VAR __DS0Core =  
    SUMMARIZECOLUMNS( 
      ROLLUPADDISSUBTOTAL('Table'[col1], "IsGrandTotalRowTotal"), 
      __DS0FilterTable, 
      "Reach_", 'Table'[Reach%], 
      "Index", 'Table'[Index] 
    ) 
  VAR __DS0PrimaryWindowed =  
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Table'[col1], 1) 
EVALUATE 
  __DS0PrimaryWindowed 
ORDER BY 
  [IsGrandTotalRowTotal] DESC, 'Table'[col1]

     

    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • miss_stoat's avatar
    miss_stoat
    Frequent Visitor
    3 years ago

    Hi v-kkf-msft 

    Thank you for taking the time to reply to me 

    Do I need to do these parameters as a separate dataset or do I try and add it into the DAX query already there? below is a screenshot of the current DAX query (there is a parameter of 'Job No' already there

    What I am trying to do is if the user selects 'Job No' in a 'consolidate by' parameter, then enters a valid job number it should then run these columns associated with the 'Job No' - simarlarly, if they select HAWB or Invoice Number 
    (then enter a vald HAWB or Invoice Number) they will get the details associated with that HAWB or Invoice Number.

    This is how the parameters look to the user in SSRS and that I am trying to replicate in PBI Report Builder

    Thanks again

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      miss_stoat did you figure out this? I am an SSRS user as well and I have been trying to figure out how to do this i power bi as well.