Forum Discussion

shavish's avatar
shavish
Helper I
1 year ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
  • Deku's avatar
    Deku
    1 year ago
    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

johnbasha33's avatar
johnbasha33
Super User
1 year ago

shavish 
Your issue is caused by how the WHERE clause 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 !!