cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
laurentalp
Frequent Visitor

Filter a column using other column in the same table

I have a table with all procedures from a hospital, but with more fields that I use in another page.

Like this:

(let's call this table 'PROCEDURES')

ProcedureRequesting doctor specialtyPerforming doctor specialtyQTDpricedate (dd/mm/yyyy)
Examcardiologycardiology210010/04/2022
Examorthopedicsorthopedics15001/04/2022
medical appointment orthopedics112003/04/2022
medical appointment psychiatry112004/04/2022
Examorthopedicsradiology14001/04/2022
Examorthopedicsradiology32004/04/2022
Examcardiologyradiology11503/04/2022
medical appointment cardiology112012/04/2022

 

In the first visual, I have a sum of all medical appointments by Performing Dr. (using a measure to filter proceduresand sum QTD and a calendar table):

laurentalp_2-1652381152206.png


Measure is (simplified) like this:

appointment =
calculate
(
SUM(PROCEDURE[QTD]),
USERELATIONSHIP('#Calendar'[Dates],PROCEDURES[DATE]),
PROCEDURES[Procedure] IN{
'medical appointment'},
FILTER('#Calendar','#Calendar'[LM]="TRUE") -- this part is for filter only the last month
)
 

In the next visual I have all exams:

laurentalp_4-1652381232084.png

 

Usign a measure, similar to the first, but filtering Exams.

 

But if I choose a colunm in the first, it will filter Exams by Perfoming Doctor, which is not what I want.

 

I want to click on the first visual which is ordered by "Performing doctor specialty" and filter only exams using as "Requesting doctor specialty".

 

Example:

Perfoming Doctor:

laurentalp_5-1652381526734.png

 

In a table, it will filter this:

Procedure Kindspecialty Requesting doctorspecialty Performing doctor QTDpricedate
Examcardiologycardiology210012/04/2022
Examcardiologyradiology11512/04/2022

 

laurentalp_6-1652381676822.png

 

I'm thinking about a measure Exam that use Performing Doctor filter from the first as Specialty Doctor in the second, is it possible?

1 ACCEPTED SOLUTION
laurentalp
Frequent Visitor

I achieved the result using other solution:
Instead all procedures in one table, I made one table with only medical appointments and other with exams and other procedures, and created a relationship between performing doctor on table A and requesting doctor on table B.

View solution in original post

7 REPLIES 7
laurentalp
Frequent Visitor

I achieved the result using other solution:
Instead all procedures in one table, I made one table with only medical appointments and other with exams and other procedures, and created a relationship between performing doctor on table A and requesting doctor on table B.

v-yalanwu-msft
Community Support
Community Support

Hi, @laurentalp ;

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft
Community Support
Community Support

Hi, @laurentalp ;

You could share a simple file and show the result what you want to output.


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft
Community Support
Community Support

Hi, @laurentalp ;

You should add another table from [Performing doctor specialty] column ;

filter = VALUES('Table'[Performing doctor specialty])

Then create a flag measure.

flag = 
IF (
    ISFILTERED ( 'filter'[Performing doctor specialty] ),
    IF (
        MAX ( 'Table'[Requesting doctor specialty] )
            IN ALLSELECTED ( 'filter'[Performing doctor specialty] ),
        1,
        0
    ),
    1
)

add the visual filter.

vyalanwumsft_0-1652766945252.png

The final show:

vyalanwumsft_1-1652766961554.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your reply.
It's almost what I need, but in first table I need total appointments per "Performing doctor specialty" too, not only use as a filter. I tried to use a relationship, but it use the relationship for filter, which i don't want to.

amitchandak
Super User
Super User

@laurentalp , visual will only pass the non summarized column and ant slicer. In filter forced in measure will not pass. for that you can use the measure in first visual as visual level filter in second visual

Thank you by your reply, I will try another way

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors