Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Finding the distinct IDs from filtered data

Measure Trainees Completed Course =
CALCULATE (
COUNTROWS(Training),
FILTER (
Training,
SEARCH ( "Completed", Training[Status],, BLANK () ) > 0
|| SEARCH ( "Complete", Training[Status],, BLANK () ) > 0
)
)
 
From my measure above, I'm trying to determine the count of unqiue traineeIDs that fulfill the filter requirements. I've tried applying the distinct function to the COUNTROWS(Training) and selecting for the trainee ID column in the training table, while applying the ALL function inside the FILTER function but that didn't work. How would I go about getting only the distinct values for this calculation?
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

this did not work for you ?

CALCULATE (
distinctcount(Training[trainee id]),
FILTER (
Training,
SEARCH ( "Completed", Training[Status],, 0 ) > 0
|| SEARCH ( "Complete", Training[Status],, 0) > 0
)
)

View solution in original post

vanessafvg
Super User
Super User

@Anonymous  not sure i quite understand what you asking, did you say you tried this

Measure Trainees Completed Course =
CALCULATE (
    DISTINCTCOUNT ( [trainingid] ),
    FILTER (
        Training,
        SEARCH ( "Completed", Training[Status],, BLANK () ) > 0
            || SEARCH ( "Complete", Training[Status],, BLANK () ) > 0
    )
) but it didn't work? What are you trying to do?  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
vanessafvg
Super User
Super User

@Anonymous  not sure i quite understand what you asking, did you say you tried this

Measure Trainees Completed Course =
CALCULATE (
    DISTINCTCOUNT ( [trainingid] ),
    FILTER (
        Training,
        SEARCH ( "Completed", Training[Status],, BLANK () ) > 0
            || SEARCH ( "Complete", Training[Status],, BLANK () ) > 0
    )
) but it didn't work? What are you trying to do?  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




amitchandak
Super User
Super User

this did not work for you ?

CALCULATE (
distinctcount(Training[trainee id]),
FILTER (
Training,
SEARCH ( "Completed", Training[Status],, 0 ) > 0
|| SEARCH ( "Complete", Training[Status],, 0) > 0
)
)
Anonymous
Not applicable

Yes it worked, thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.