Calculated Columns: DAX Code

[HenryJS]

Hi all,

Please could someone advise the DAX code for the below required calculated columns:

Column 1:

• Consultant (simple - drag UserName to Rows)

Column 2: DAX Required

• Open Jobs = Count of JobRef where JobStatus = Open

Column 3: DAX Required

• Advertised Open Jobs = Count of JobRef where HasAdvert = Yes

Column 4: DAX Required

• % Jobs Advertised = Column 2 / Column 3 * 100

Columns 2-4 need to relate to that consultant.

 

Thanks,

Henry

[v-yuta-msft]

@HenryJS ,

Could you please share more details about "Columns 2-4 need to relate to that consultant."?

Regards,

Jimmy Tao

[az38]

Hi @HenryJS 

try

Open Jobs = CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table','Table'[UserName]), 'Table'[JobStatus]="open")

Advertised Open Jobs =  CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table','Table'[UserName]), 'Table'[HasAdvert]="yes")

% Jobs Advertised = [Open Jobs]/[Advertised Open Jobs]

then for column % Jobs Advertised set data type as percent in Modeling ribbon

do not hesitate to give a kudo to useful posts and mark solutions as solution

[HenryJS]

Hi @az38 

Instead of Count Rows, how do I make it output a '1' if the expressions we input are true?

Thanks,

Henry

[az38]

@HenryJS 

try

Open Jobs = CALCULATE(COUNTX('Table'[JobRef]), ALLEXCEPT('Table','Table'[UserName]), 'Table'[JobStatus]="open")

do not hesitate to give a kudo to useful posts and mark solutions as solution