Filter a column using other column in the same table

[laurentalp]

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')

Procedure	Requesting doctor specialty	Performing doctor specialty	QTD	price	date (dd/mm/yyyy)
Exam	cardiology	cardiology	2	100	10/04/2022
Exam	orthopedics	orthopedics	1	50	01/04/2022
medical appointment		orthopedics	1	120	03/04/2022
medical appointment		psychiatry	1	120	04/04/2022
Exam	orthopedics	radiology	1	40	01/04/2022
Exam	orthopedics	radiology	3	20	04/04/2022
Exam	cardiology	radiology	1	15	03/04/2022
medical appointment		cardiology	1	120	12/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):

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:

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:

In a table, it will filter this:

Procedure Kind	specialty Requesting doctor	specialty Performing doctor	QTD	price	date
Exam	cardiology	cardiology	2	100	12/04/2022
Exam	cardiology	radiology	1	15	12/04/2022

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

[laurentalp · Accepted solution]

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]

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]

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]

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.

The final show:

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.

[laurentalp]

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]

@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

[laurentalp]

Thank you by your reply, I will try another way