Forum Discussion
SQL query- Power query BI help
I have following query with 5 parameters. When i select all 5 parameters, i see the entry is shown in Power BI desktop. If any of the selection is missing, the values aren't shown.
My requirement: On Default, i want to show the entire table and upon any parameter selection, respective entry shall be shown. Can anyone help?
The parameters are: Confidential_Flag , Doc_Name, Doc_Desc , Category and Usage
SELECT DISTINCT
Document.Name as Name
Document.Desc as Description
Document.Category as Category
Document.Usage as Usage
Document.No_of_pages as No_of_Pages
Document.Author_name as Author
Document.Confidential as Confidential
FROM
(SELECT
tblProperty.Name as Name
tblProperty.Desc as Description
tblProperty.Category as Category
tblPropertyument.Usage as Usage
tblProperty.No_of_pages as No_of_Pages
tblProperty.Author_name as Author
tblProperty.Confidential as Confidential
FROM
Docsdb.dbo.tblProperty tblProperty
)Document_
WHERE
(CASE WHEN (('" &Confidential_Flag &"'='1') AND (Document.Confidential='1')) THEN '1' WHEN(('" &Confidential_Flag &"='0' AND Document.Confidential='0' THEN '0')
AND ('" &Doc_Name &"' is NULL OR Document.Name IN ('" &Doc_Name &"'))
AND ('" &Doc_Desc &"' is NULL OR Document.Desc IN ('" &Doc_Desc &"'))
AND ('" &Category&"' is NULL OR Document.Category IN ('" &Category&"'))
AND ('" &Usage&"' is NULL OR Document.Usage IN ('" &Usage&"'))
SELECT DISTINCT Name, Desc as Description, Category, Usage, No_of_Pages, Author, Confidential FROM Docsdb.dbo.tblProperty WHERE (CASE WHEN '" & Confidential_Flag &"'='1' AND Confidential='1' THEN 1 WHEN '" & Confidential_Flag &"='0' AND Confidential='0' THEN 0 ) AND ('" & Doc_Name &"' = '' OR Name = '" &Doc_Name &"') AND ('" & Doc_Desc &"' = '' OR Desc = '" &Doc_Desc &"') AND ('" & Category &"' = '' OR Category = '" &Category&"') AND ('" & Usage &"' = '' OR Usage = '" &Usage&"')You were wrapping the catergories in '' so blanks would of always been empty strings. Try the above
7 Replies
johnbasha33 My query worked on SQL, FYI. I will try your solution.
shavish
Your issue is caused by how theWHEREclause is structured. The current logic requires all parameters to be selected for the query to return results. You need to modify it so that when parameters are NULL, the query returns all records.
Use ISNULL() or COALESCE() to ensure that when a parameter is not provided, it does not filter the results.
Fixed SQL Query:
SELECT DISTINCT
Document.Name AS Name,
Document.Desc AS Description,
Document.Category AS Category,
Document.Usage AS Usage,
Document.No_of_pages AS No_of_Pages,
Document.Author_name AS Author,
Document.Confidential AS Confidential
FROM Docsdb.dbo.tblProperty AS Document
WHERE
(ISNULL(@Confidential_Flag, '') = '' OR Document.Confidential = @Confidential_Flag)
AND (ISNULL(@Doc_Name, '') = '' OR Document.Name IN (SELECT value FROM STRING_SPLIT(@Doc_Name, ',')))
AND (ISNULL(@Doc_Desc, '') = '' OR Document.Desc IN (SELECT value FROM STRING_SPLIT(@Doc_Desc, ',')))
AND (ISNULL(@Category, '') = '' OR Document.Category IN (SELECT value FROM STRING_SPLIT(@Category, ',')))
AND (ISNULL(@Usage, '') = '' OR Document.Usage IN (SELECT value FROM STRING_SPLIT(@Usage, ',')));
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!SELECT DISTINCT Name, Desc as Description, Category, Usage, No_of_Pages, Author, Confidential FROM Docsdb.dbo.tblProperty WHERE (CASE WHEN '" & Confidential_Flag &"'='1' AND Confidential='1' THEN 1 WHEN '" & Confidential_Flag &"='0' AND Confidential='0' THEN 0 ) AND ('" & Doc_Name &"' = '' OR Name = '" &Doc_Name &"') AND ('" & Doc_Desc &"' = '' OR Desc = '" &Doc_Desc &"') AND ('" & Category &"' = '' OR Category = '" &Category&"') AND ('" & Usage &"' = '' OR Usage = '" &Usage&"')You were wrapping the catergories in '' so blanks would of always been empty strings. Try the above
Hi shavish ,
Thank you for the update. It's encouraging to know your query works in SQL. However, Power BI may handle parameter filtering differently. The solution provided by johnbasha33 , ensures that when parameters are not selected, all records are displayed rather than filtered out. Please try this fix in Power BI and inform us if you encounter any issues.
Thank you for your responses johnbasha33 & Deku .
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi shavish ,
We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.
Your feedback is valuable to us, and we look forward to hearing from you soon.
Thanks..
Hi shavish ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi shavish ,
Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.
