Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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...
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
@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.
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.
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
27 |
User | Count |
---|---|
99 | |
91 | |
51 | |
48 | |
47 |