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

Deku's avatar
Deku
Super User
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