Dynamic Parameters in PBI Report Builder using a published dataset

[miss_stoat]

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

[miss_stoat]

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]

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

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.