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 param...
Need Help
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.