Forum Discussion

aleph_heideger's avatar
aleph_heideger
Frequent Visitor
8 years ago
Solved

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?

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

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

12 Replies

Replies have been turned off for this discussion
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        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.

         

         

         

  • Drors's avatar
    Drors
    Resolver III
    8 years ago

    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.