Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

More Filter Options

Hi, I Connected my SQL Database to PwerBi and Joined a Few Tables. Now the Table Contains to an ID a Questions and a Answers.  I want to Display ONLY the IDs where the Answer of a Questions is "Ja"...
Need Help
  • fhill's avatar
    fhill
    5 years ago

    More Screen Shot Examples:

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks for the Answers. More Informations about the Table.

The Database is from a Hospital in Germany and it Contains much more (like Names and Adresses).

The Patient fill a digital Anamnesis on a Tablet and that are the Answers from the SQL Server. 

Now the Doctor want to know (for Example) how many People are older than 65 years AND have marked the Question "Problems with Stairs Upwalking" with "Yes" (ja) AND "Do you have an Artificial Joint ?" with "Yes"

 

Every New Line in the Table starts withe the PID (Patient ID) Name, Question and  Answer

In the Picture above I wanted to see all PID's where the Answer of the Question "Heart/Cardio Disease " is "Yes"  AND the same PID answerd the Question "Do you have Tattos or Piercings? " with "Yes". It should only Displayed 4 Lines now(2x the same PID) or maybe 2 Lines (Same PID in 1 Line)

 

I know SQL Language but the Doctors don't but they are Technically interested

 

It is Possible to show that with Filters? Maybe on an new Table or new Column?

fhill's avatar
fhill
Resident Rockstar
5 years ago

Ok, give this a try....

 

In line with the last post, we still need to create a Table of Selected (Slicer) Q & A Combined Values:

Calculated Column on Original Data Table
Q and A = 'Table'[Questions] & " - " & 'Table'[Answers]

(Modeling Tab -> New Table  /  And

slicer table = VALUES('Table'[Q and A])

 

Now we create several meausre again on the Original Data Table:

(I changed the delimiter to a Pipe charcter instead of Comma, use whatever character works for your data...)

Selected Symptoms = CONCATENATEX(ALLSELECTED('slicer table'),'slicer table'[Q and A]," | ")
 
(Count of Selected Symptoms from the Slicer Table - Will be used to compare later)
Selected Count = DISTINCTCOUNT('slicer table'[Q and A])
 

(If this ROW of Question - Answer combination was FOUND in the Selection / Slicer Table)

Found = CONTAINSSTRING([Selected Symptoms], FIRSTNONBLANK('Table'[Q and A],""))
 
(By UID / Name, SUM Found matches)  P.S.  Likly, some of these Measures could be combined together, I just work in small steps...
UID Symtop Count = CALCULATE( COUNT('Table'[Name]), FILTER('Table', [Found] = TRUE()))
 
(Last, Final Check comparing the Count of Symptoms Selected to the FOUND / Matching Questions & Answer combinations BY Name / UID)
FINAL CHECK = IF( [Selected Count] = [UID Symtop Count], "Matches All Conditions")
 
Make sure your 'results visual' Filters 'Final Check' not to be Blank, and ***** You can select any Data Column that's the SAME for every UID, but I had to use 'Selected Symptoms' in the visual instead of the indivudal Question & Answer Columns, because anything that would result in more than 1 row per UID/Name will throw off the 'Final Check' Filter logic...
 
Hope this helps, or at least gets you closer to your solution.
FOrrest

 

  • fhill's avatar
    fhill
    Resident Rockstar
    5 years ago

    More Screen Shot Examples: