DAX: Count Multiple Column Values

[HenryJS]

Hi all,

I want to create a measure that counts if Reference1, Reference2 and Reference3 has a value in the field?

For example the top row would be 2 as Reference1 and Reference2 have a value in field.

[v-kelly-msft · Accepted solution]

Hi @HenryJS ,

Does "N/A" mean a value, if so ,you need a calculated column as below:

Column = IF('Table'[Reference 1]=BLANK(),0,1)+IF('Table'[Reference 2]=BLANK(),0,1)+IF('Table'[Reference 3]=BLANK(),0,1)

Otherwise,a calculated column as below:

Column 2 = IF('Table'[Reference 1]=BLANK() || 'Table'[Reference 1]="N/A",0,1)+IF('Table'[Reference 2]=BLANK() || 'Table'[Reference 2]="N/A",0,1)+IF('Table'[Reference 3]=BLANK() || 'Table'[Reference 3]="N/A",0,1)

And you will see :

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

[az38]

Hi @HenryJS 

its better to create calculated column, not measure, like

Column = 
IF((ISBLANK([Reference1]), 0, 1) + 
IF((ISBLANK([Reference2]), 0, 1) + 
IF((ISBLANK([Reference3]), 0, 1)

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

LinkedIn