Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
miss_stoat
Frequent Visitor

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...

3 REPLIES 3
miss_stoat
Frequent Visitor

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

miss_stoat_0-1662536255118.png

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

miss_stoat_1-1662536518870.png

Thanks again

@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. 

v-kkf-msft
Community Support
Community Support

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] 

vkkfmsft_0-1662531576567.png

vkkfmsft_1-1662531656644.png

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.