Identifying missing data

[maurcollins]

Good Morning

I need some help please with identifying where there is missing data in a table.

Example of the data is 

Learner	Applilcation	Course
1	01/01/2022	A1
1	01/01/2022	B1
2	01/01/2022	B1
3	01/01/2022	A2
3	01/01/2022	B2

All learners should have a course beginning with an A, i need to idenitfy from the data the ones that dont.

So in the example above it would identify learner 2.

The applications will differ from those in the table.

Thank you for your help.

[maurcollins]

Thank you

[Mikelytics · Accepted solution]

Hi @maurcollins 

Please try the following:

Base Table

Create a new Meausre

CourseWithA = 
var var_numberOfCoursesWithA =
    SUMX(
        FactTable,
        IF(LEFT(FactTable[Course],1) ="A",1,0)
    )

RETURN
IF(var_numberOfCoursesWithA>0,1,0)

put learner and the measure into a visual

you can also use the measure as a filter

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn