cancel
Showing results for
Did you mean:
aleph_heideger Frequent Visitor

## Filtering data in table visual

So, hello again.

Need some assistance in something that I really don't know if it's possible at all.

I work for a diabetes clinic and I'm the guy that does the Excel stuff. Things like how many patients we have at the moment, demographics, diagnosis. You name it. I'm trying to do the same things in PowerBI beause I find the visuals much more appealing to my colleagues.

I have a dashboard that summarizes some of our data. In turn, my data (for the example) is as follow:

• Doctor
• Patient_ID
• Diagnosis

Using just these three fields I can see how many patients some doctor has enrolled or how many have A or B diagnosis.

Using the measure in here https://community.powerbi.com/t5/Desktop/Help-with-sorting-patients-acording-to-diagnosis/m-p/270210 I can even calculate how many patients have at the same time diagnosis A and B (I believe that PowerBI usually calculates using OR as logic).

Now what I'm trying (and failling miserably) is to get a table to show the selected patients ID. If I click diagnosis A and B and I get a calculated number of 5 I would like to know who are these 5. Instead the table gives me all the patients that have diagnosis A OR B.

So far in my search I wasn't able to find anything of use.

Can someone share a thought here?

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: Filtering data in table visual

OK, this one will work for 2 selections.

```Measure =
VAR tmpTable1 = FILTER(Problemas_ICPC,Problemas_ICPC[ICPC]=MAX([ICPC]))
VAR tmpTable2 = FILTER(Problemas_ICPC,[ICPC]=MIN([ICPC]))
VAR tmpTable1a = SELECTCOLUMNS(tmpTable1,"Utente",[Utente])
VAR tmpTable2a = SELECTCOLUMNS(tmpTable2,"Utente",[Utente])
VAR tmpTable3 = NATURALINNERJOIN(tmpTable1a,tmpTable2a)
RETURN CONCATENATEX(tmpTable3,[Utente],",")```

I anticipate that you will want it for 3 or more selections.

Oh wait, this works:

```Doentes_todos_diagnosticos_utentes =
VAR tmpTable =
EXCEPT (
VALUES ( Problemas_ICPC[Utente] ),
SUMMARIZE (
GENERATE (
VALUES ( Problemas_ICPC[Utente] ),
EXCEPT (
VALUES ( Problemas_ICPC[ICPC] ),
CALCULATETABLE ( VALUES ( Problemas_ICPC[ICPC] ) )
)
),
Problemas_ICPC[Utente]
)
)
RETURN CONCATENATEX(tmpTable,[Utente],",")```

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

12 REPLIES 12 Super User

## Re: Filtering data in table visual

Hi,

Create a Table visual with Patients in the row labels and the measure (which computed the patients that have undergone diagnoses A and B) in the value area section.  If this does not work, then share the link from where i can download your PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Drors Member

## Re: Filtering data in table visual

You need to create a table visual, the put the Patient_ID in the values, then press in the small arrow neer it and choose "Dont summarize" , then, create a new slicer visual with the Diagnosis.

now, when you will filter the ID's in the table should be according to you selection without any aggregetion.

Highlighted
aleph_heideger Frequent Visitor

## Re: Filtering data in table visual

Hello again.

I tried both solutions but I still couldn't figure a way to make it work.

I'll leave a test file here hoping someone could help out. Super User

## Re: Filtering data in table visual

@aleph_heideger- I believe that the formula that you want is:

```Doentes_todos_diagnosticos_utentes =
VAR tmpTable =
EXCEPT (
VALUES ( Problemas_ICPC[Utente] ),
SUMMARIZE (
GENERATE (
VALUES ( Problemas_ICPC[Utente] ),
EXCEPT (
VALUES ( Problemas_ICPC[ICPC] ),
CALCULATETABLE ( VALUES ( Problemas_ICPC[ICPC] ) )
)
),
Problemas_ICPC[Utente]
)
)
RETURN CONCATENATEX(tmpTable,tmpTable,",")```

I attached the modified PBIX file.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

aleph_heideger Frequent Visitor

## Re: Filtering data in table visual

It does work but only when the output is only one patient; if the result is, let's say, 4 (F91 + K86) the output is a error:

Error Message:
MdxScript(Model) (35, 30) Calculation error in measure 'Problemas_ICPC'[Doentes_todos_diagnosticos_utentes]: A table of multiple values was supplied where a single value was expected.

Instead I was expecting 4 ID's such as (fictitious id's for example):

174125954

174365988

165888942

569888762

Your solution only works if the output is 1 Super User

## Re: Filtering data in table visual

Before I go further on this, can you clarify what you want or at least confirm my understanding.

So, in the example where the Inscrutos_medico slicer is set to "Ana" and the Problems_ICPC is se to K85 and K86 do you want:

1. The list of patients that are "Ana" AND are "K85" AND are "K86"
2. The list of patients that are "Ana" AND "K85" OR "K86"
3. Something else?

If something else, please clarify. Thanks.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

aleph_heideger Frequent Visitor

## Re: Filtering data in table visual

@Greg_Deckler

What I want to achieve is a table showing all the ID's that are:

1. The list of patients that are "Ana" AND are "K85" AND are "K86"

However, your solution works if there is only on patient with hose conditions. If there are more I get the mentioned error. Is the test file you set could you try to get a table wit all the patients that are

• "Ana" AND are "F91" AND are "K86"

It should output a list with 4 patient numbers. But it's giving an error. Super User

## Re: Filtering data in table visual

OK, now that I know what you are looking for, let me take a look.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut! Super User

## Re: Filtering data in table visual

OK, this one will work for 2 selections.

```Measure =
VAR tmpTable1 = FILTER(Problemas_ICPC,Problemas_ICPC[ICPC]=MAX([ICPC]))
VAR tmpTable2 = FILTER(Problemas_ICPC,[ICPC]=MIN([ICPC]))
VAR tmpTable1a = SELECTCOLUMNS(tmpTable1,"Utente",[Utente])
VAR tmpTable2a = SELECTCOLUMNS(tmpTable2,"Utente",[Utente])
VAR tmpTable3 = NATURALINNERJOIN(tmpTable1a,tmpTable2a)
RETURN CONCATENATEX(tmpTable3,[Utente],",")```

I anticipate that you will want it for 3 or more selections.

Oh wait, this works:

```Doentes_todos_diagnosticos_utentes =
VAR tmpTable =
EXCEPT (
VALUES ( Problemas_ICPC[Utente] ),
SUMMARIZE (
GENERATE (
VALUES ( Problemas_ICPC[Utente] ),
EXCEPT (
VALUES ( Problemas_ICPC[ICPC] ),
CALCULATETABLE ( VALUES ( Problemas_ICPC[ICPC] ) )
)
),
Problemas_ICPC[Utente]
)
)
RETURN CONCATENATEX(tmpTable,[Utente],",")```

### I have book! Learn Power BI from Packt 