Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to 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],",")
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.
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.
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.
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:
If something else, please clarify. Thanks.
What I want to achieve is a table showing all the ID's that are:
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
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.
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],",")
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:
Isn't it possible to create a table using something like this?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |