cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aleph_heideger Frequent Visitor
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

12 REPLIES 12
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
Drors Member
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. 

aleph_heideger Frequent Visitor
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.

 

https://drive.google.com/file/d/1h_OAFbc_oKq2uHHjGYVhvZzmd-xqv19B/view?usp=sharing

 

Thanks in advance for all your help.

Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
aleph_heideger Frequent Visitor
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

aleph_heideger Frequent Visitor
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,060)