Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a PatientID slicer/filter and a check box that mentioned "Included Dependent" in my report. Blue Table is my table structure. Green Table shows the desired output when I filtered PatientID and whether or not I check the "Included Dependent".
For example, when I filter 'A1' in PatientID and I checked the "Included Dependent", then it will return 4 Patients records (A0, A2, A3, A4). On the other hand, if I didn't checked the "Included Dependent", then it only show 'A1' record.
Anyway that I can apply to all visuals in my report?
Thank you.
Solved! Go to Solution.
Hi @PowerBIOrg ,
Here's my solution.
1.Create a calculated table to get the unique Patient ID for the slicer.
Table 2 = DISTINCT('Table'[PatientID])
2.Create another table with the following two values for the slicer too by entering data.
3.There're no relationships among tables.
4.Create a measure as a visual level filter.
Measure =
SWITCH (
SELECTEDVALUE ( 'Table 3'[Type] ),
"Included Dependent",
IF (
CALCULATE (
MAX ( 'Table'[EmployeeID] ),
FILTER (
ALLSELECTED ( 'Table' ),
[EmployeeID] = MAX ( 'Table'[EmployeeID] )
&& [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] )
)
)
= MAX ( 'Table'[EmployeeID] ),
1
),
"Not Included Dependent",
IF (
CALCULATE (
MAX ( 'Table'[PatientID] ),
FILTER (
ALLSELECTED ( 'Table' ),
[EmployeeID] = MAX ( 'Table'[EmployeeID] )
&& [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] )
)
)
= MAX ( 'Table'[PatientID] ),
1
)
)
5.Create two slicers, a table visual as follows.
6.Put the measure into the table visual level filters, set show items when the value is 1.
Now, when you select 'Included Dependent', 'A0' and 'B0', the result is below.
When you select 'Not Included Dependent', 'A0' and 'B0', the result is below.
You can check more details from my attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PowerBIOrg ,
One question, where is A4 in your input table?
According to your description, I temporarily get the following results.
You can filter the Paitent ID.
You can download my attachment for details.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, I'm sorry that making you all confuse. I actually want the output like the following example:
If I choose A0, A1, A2 or A3 patient and ticked "Included Dependent", it will return the records of Employee A0. For example: if I choose A2, then it will show A0 Employees records (which included all A0, A1, A2, A3 patients records).
If I choose A0, A1, A2 or A3 patient and ticked "Not Included Dependent", it will return the records of selected patient only. For example: if I choose A1, then it will show A1 patient records only.
Hi @PowerBIOrg ,
Here's my solution.
1.Create a calculated table to get the unique Patient ID for the slicer.
Table 2 = DISTINCT('Table'[PatientID])
2.Create another table with the following two values for the slicer too by entering data.
3.There're no relationships among tables.
4.Create a measure as a visual level filter.
Measure =
SWITCH (
SELECTEDVALUE ( 'Table 3'[Type] ),
"Included Dependent",
IF (
CALCULATE (
MAX ( 'Table'[EmployeeID] ),
FILTER (
ALLSELECTED ( 'Table' ),
[EmployeeID] = MAX ( 'Table'[EmployeeID] )
&& [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] )
)
)
= MAX ( 'Table'[EmployeeID] ),
1
),
"Not Included Dependent",
IF (
CALCULATE (
MAX ( 'Table'[PatientID] ),
FILTER (
ALLSELECTED ( 'Table' ),
[EmployeeID] = MAX ( 'Table'[EmployeeID] )
&& [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] )
)
)
= MAX ( 'Table'[PatientID] ),
1
)
)
5.Create two slicers, a table visual as follows.
6.Put the measure into the table visual level filters, set show items when the value is 1.
Now, when you select 'Included Dependent', 'A0' and 'B0', the result is below.
When you select 'Not Included Dependent', 'A0' and 'B0', the result is below.
You can check more details from my attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Stephen!
Though it didn't meet my desired output for "Not Included Dependent" part, so I modified the query as following:
IF (
CALCULATE(MAX('Table'[PatientID]),
FILTER ('Table', [PatientID] IN ALLSELECTED ( 'Table 2'[PatientID] ) ) )
= MAX ( 'Table'[PatientID] ),
1
)
@PowerBIOrg , You can create a new column and use that in the matrix
if([relationship]<> "Employee", "Included Dependent", "not Included Dependent")
Hi @amitchandak ,
Not only when the relationship is Employee then only Included Dependent.
For Example, when we selected the child A1 of Employee A0, and we want to see the dependent records, then we want it to return all the records of the Employee (A0) and the Employee's Dependents (A1,A2,A3).
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |