More Filter Options

[Zweier]

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" (Yes) AND the Answer of the other Question is "Ja". In the Future there should be 4 Questions and some of them Contains other Answers like Numbers.

Now it shows any Answer with "ja" but i want only the Red and the Green One to be displayed in the Visual.

There is no Clickable Filter who can do that? I need the Measure  /DAX for that?

For any new Filter i want i need to write a new DAX ?

Thanks for Help 

[Zweier]

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]

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 · Accepted solution]

More Screen Shot Examples:

[fhill]

I modified this post to get what I think you need:

https://community.powerbi.com/t5/Desktop/Filter-on-ALL-selected-values-instead-of-default-AT-LEAST-O...

First create a New Table with ONLY this one Value, and make sure it's NOT Linked to the original table

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

Now create this COLUMN on the original Table:  (It Concatenates the Questions and Answers Together)

Q and A = 'Table'[Questions] & " - " & 'Table'[Answers]

Now create these Measures on the Original Table:

(It produces a Measures of ALL Selected (From the Slicer Table) Q & A Options)

selected symptom = CONCATENATEX(ALLSELECTED('slicer table'),'slicer table'[Q and A],",")

We will use this Meausre if 'Found' as a filter of the final data, to only show those with Selected Answers

Found = IF( CONTAINSSTRING([selected symptom], FIRSTNONBLANK('Table'[Q and A],"")), "Found")

**** I just realized this GIVES everyone, and not the COMBINATION of Q & A's selected...  I'll try to work on it again later today, but I have family stuff now for the next few hours...  If anyone else can jump back in to finish, please help... ****

[fhill]

Consider this process instead...   I'm guessing you only want UID's where ALL Conditions are 'ja', and the Red UID has an 'nein' somewhere not shown on the example?

Instead of filtering on Antworten, Create a new column called 'Summary Check' and put your logic with IF / THEN statements into this column.  (This will also help when you have 4 different LOGIC CHECKS to get a true 'Ja' / 'Nein' at the end of the 'summary Check' Column logic, not just one....)

NOW, since i see multiple UID numbers in your example, i'm going to assume SOME rows fo data will fail the check, and some will pass.  Are you looking for UIDs where ALL ROWS pass?

If So....  you'll need to create a new table with only Unique UID values.  Use this new table to 'COUNT UID' and "COUNT CHECK'.  Create a 'Final Check' of UID Count = Check Count, then THAT UID is finally the filter to be used to get only good UIDs.

For example (again going based on my assumption) - UID 907216 has 3 total rows, and only 1 of the 3 rows has 'ja' in the 'Summary Check' coulmn.  In the summary table, this UID (listed only once - remove duplicates) will have 'COUNT UID' of 3, and 'COUNT Check' of 1.  Thses don't match, so 'Final Check' will fail 'final nein'.

UID 9103836 has 5 total rows, and all 5 have 'ja' in the 'Summary Check' Column.  COUNT UID will be 5, and COUNT CHeck will be 5, NOW this UID will pass and 'Final Check' will be 'final ya'.  

NOW, you can use this Table with the 'Final ya' filter, to join back only good UIDs to your Summary table to pull any data you need from ALL UID rows as needed.

[AlB]

Hi @Zweier 

In the same wy that you created a filter for the answer to be "Ja", you can create a filter on the middel column seelcting only the two questions taht you want to show (with an or)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@Zweier , You have already filtered on Ja, Does the question has more than one answer and all of them need to be Ja?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.