Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

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],",")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
Drors
Resolver III
Resolver III

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. 

Ashish_Mathur
Super User
Super User

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/

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.

@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.

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@

 

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

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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],",")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

 

Well, Your second solution works perfectly for what I want.

 

Only (minor) problem is that outputs all data in a single line, ID's separated by a comma sign. But like I said, a diferent problem for later - output a real table.

 

Next step is trying to understand your code so I can at least learn something from you.

 

Thanks.

I posted a simplified version here that only has the necessary elements so that other people can study it:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort/m-p/391883

 

You could use the same code and just return the "tmpTable3" (RETURN tmpTable3) and use that as a Table formula. However, it is not dynamic. 😞

 

Maybe one day we will get a Table visualization that you can tie a dynamic table to, that would be really cool. I don't think that exists as a custom visual that I have found, but might be worth checking into. Then you could just do an export. 

 

I did solve this once so that you could generate a dynamic table visualization from the source table. But it used an user input table and some filtering. If I get a chance I'll try to dig it up as an example. Problem was that it solved being able to easily export but wasn't nearly as nice of a user interface and took some setup.

 

I posted this as an Idea here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33890335-table-visualization-tied...

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

 

Isn't it possible to create a table using something like this?

https://community.powerbi.com/t5/Desktop/table-with-dynamic-values-columns/td-p/110266/highlight/tru...

 

I'm not at my computer right now so can't test it but it appears something in the right direction: showing in a table visual filtered results.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.