How to determine dual registrations

[Anonymous]

Hi,

I am new to Power BI and at the moment trying to figure out how to detemine dual registrations of teacher data that we have on CRM online.

We've got 'Registration' data of teachers. Some are only 'teachers' and some are only 'ECTs's. But some are 'Dual's meaning they have bot 'Teacher' and 'ECT' registrations.

What I want is a new column  ( like "Reg Type_2" as depicted below)  indicating whether a teacher is purely a Teacher/ ECT or a Dual. If they are 'dual' they have two registartions records in the registration table with their respective registration type under the same registration number. 

Pls see below a sample data set.

Contact	Reg Number	Reg Type	Reg type_2
Aaron	707012	Teacher	Dual
Aaron	707012	ECT	Dual
Gillespie	701252	Teacher	Teacher
Harper	704265	ECT	ECT
Houben	702153	Teacher	Dual
Houben	702153	ECT	Dual

I want to use this column is further analysis. 

Thank you very much!

Losh

[ChandeepChhabra · Accepted solution]

@Anonymous : Please try this calculated column formula

=IF(COUNTROWS(FILTER(ALL(Data),Data[Reg Number]=EARLIER(Data[Reg Number])))>1,"Dual",Data[Reg Type])

 

You download the powerpivot file here

Hope this helps

[ChandeepChhabra · Accepted solution]

@Anonymous : Please try this calculated column formula

=IF(COUNTROWS(FILTER(ALL(Data),Data[Reg Number]=EARLIER(Data[Reg Number])))>1,"Dual",Data[Reg Type])

 

You download the powerpivot file here

Hope this helps

[Anonymous · Accepted solution]

Thanks so much! very much apprecaite your help 🙂

Very impressed what a small formula can do. 

Could you kindly direct me to learn much more on these kind of furmulae?  

What does this specific one do in this particular instance?

[ChandeepChhabra]

@Anonymous You are welcome

You can take a look at microsoft documentation on how dax expressions work 

https://msdn.microsoft.com/en-us/query-bi/dax/dax-function-reference

[Anonymous]

Thanks a lot again! 🙂