Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi All
Im requiring assistance in the following.
I have a table with an employee id and a list of services assosciated with that ID.
What i am looking at acheiving is filtering for ID's that only contain services with CWB and HM only . All other ID's that have additional services to be filtered out
Date Service ID
2024-12-31 CWB AC001637888
2024-12-31 HM AC001637888
2024-12-03 CWB AC006108798
2024-12-09 HM AC006108798
2024-12-10 2HCAS AC006108798
2024-12-13 2HCAS AC006108798
2024-12-17 2HCAS AC006108798
2024-12-24 2HCAS AC006108798
2024-12-24 CCP AC006108798
2024-12-31 HCPPA AC006108798
2024-12-04 HM AC004049560
2024-12-04 HCPPA AC004049560
2024-12-09 HCPCCP AC004049560
2024-12-12 HCPCCP AC004049560
2024-12-12 PRC AC004049560
2024-12-12 HCPDA AC004049560
2024-12-18 HCPDA AC004049560
2024-12-24 HCPDA AC004049560
2024-12-30 CWB AC004049560
2024-12-31 HCPDA AC004049560
2024-12-01 HM AC000000299
2024-12-03 HM AC000000355
2024-12-05 CWB AC000000355
The expected results from the above should show ID's AC001637888 and AC000000355 as the only ones that pass the condition
Thank you
@Ashish_Mathur
Solved! Go to Solution.
Hi @James_Galis1 ,
Please update the formula of measure as below and check if it can return the correct result...
Flag =
VAR _tab =
SUMMARIZE (
'Table',
'Table'[ID],
"@count1",
CALCULATE (
DISTINCTCOUNT ( 'Table'[Service] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[Service] IN { "CWB", "HM" }
)
),
"@count2", CALCULATE ( DISTINCTCOUNT ( 'Table'[Service] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
)
VAR _id =
SELECTEDVALUE ( 'Table'[ID] )
RETURN
IF (
_id
= MAXX ( FILTER ( _tab, [@count1] = 2 && [@count2] = 2 ), [ID] ),
1,
0
)
Best Regards
I have also included the data source im working with just in case.
Note: I did get it working for 1 ID ( AC12345 ) which i entered manually on the data sheet for testing the solution
https://docs.google.com/spreadsheets/d/1cFwE9sN8HZwQZplr3aYhvxcVQlBm0ATo1VtAaKqz-dA/edit?usp=sharing
Hi @James_Galis1 ,
Please update the formula of measure as below and check if it can return the correct result...
Flag =
VAR _tab =
SUMMARIZE (
'Table',
'Table'[ID],
"@count1",
CALCULATE (
DISTINCTCOUNT ( 'Table'[Service] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[Service] IN { "CWB", "HM" }
)
),
"@count2", CALCULATE ( DISTINCTCOUNT ( 'Table'[Service] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
)
VAR _id =
SELECTEDVALUE ( 'Table'[ID] )
RETURN
IF (
_id
= MAXX ( FILTER ( _tab, [@count1] = 2 && [@count2] = 2 ), [ID] ),
1,
0
)
Best Regards
Champion !
Thank you very much
Hi @James_Galis1 ,
I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps below to get it:
1. Create a measure as below
Flag =
VAR _tab =
SUMMARIZE (
'Table',
'Table'[ID],
"@count1",
CALCULATE (
COUNT ( 'Table'[ID] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[Service] IN { "CWB", "HM" }
)
),
"@count2", CALCULATE ( COUNT ( 'Table'[ID] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
)
VAR _id =
SELECTEDVALUE ( 'Table'[ID] )
RETURN
IF (
_id
= MAXX ( FILTER ( _tab, [@count1] = 2 && [@count2] = 2 ), [ID] ),
1,
0
)
2. Create a table visual: put the field [ID] on it and add visual-level filter with the condition (Flag is 1)
Best Regards
Hi
Thank you for your time looking into this
I have tried getting this to work but it does not quite seem to work when i enter into my model.
There are plenty more dates in the report im working with and it does not quite seem to filter down to how it's expected.
There is also ID's where it's missed ( example below )maybe due to multiple dates ?
Is there a way the solution would work with multiple dates as shown above but only to show services that have CWB and HM ?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
73 | |
66 | |
60 |