Forum Discussion

Dataanalyst2's avatar
Dataanalyst2
Frequent Visitor
5 years ago

Dax - New Measure (Columns) according to multiple columns condition per row

Dear SuperUsers,

 

Currently I'm trying to add a new column with value based on other multiple columns.

For example, 

New measure (Column) = NOT FILLED if 

Column A, B, C, D, E are all Blank or Empty

And there are 20000 rows, 

 

I have below syntax but got an error saying too many argument passed to the SUM function,

 

Could someone help?

 

Thanks

Monnie

 

QC Check List Fill Rate 2 = IF(SUM(OR(ISEMPTY('OITM_MASTER'[U_LBS_QCEUOrgan]),ISBLANK('OITM_MASTER'[U_LBS_QCEUOrgan]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCAddidtives]),ISBLANK('OITM_MASTER'[U_LBS_QCAdditives]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCAnimal]),ISBLANK('OITM_MASTER'[U_LBS_QCAnimal]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCContami]),ISBLANK('OITM_MASTER'[U_LBS_QCContami]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCCOO]),ISBLANK('OITM_MASTER'[U_LBS_QCCOO]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCDateDura]),ISBLANK('OITM_MASTER'[U_LBS_QCDateDura]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCEUAppr]),ISBLANK('OITM_MASTER'[U_LBS_QCEUAppr]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCFoodC]),ISBLANK('OITM_MASTER'[U_LBS_QCFoodC]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCGenetical]),ISBLANK('OITM_MASTER'[U_LBS_QCGenetical]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCIngredients]),ISBLANK('OITM_MASTER'[U_LBS_QCIngredients]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCInstruct]),ISBLANK('OITM_MASTER'[U_LBS_QCInstruct]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCNetQt]),ISBLANK('OITM_MASTER'[U_LBS_QCNetQt]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCNutrition]),ISBLANK('OITM_MASTER'[U_LBS_QCNutrition]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCOthers]),ISBLANK('OITM_MASTER'[U_LBS_QCOthers]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCPestcid]),ISBLANK('OITM_MASTER'[U_LBS_QCPesticid]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCPreTest]),ISBLANK('OITM_MASTER'[U_LBS_QCPreTest]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCProduct]),ISBLANK('OITM_MASTER'[U_LBS_QCProduct]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCAllergies]),ISBLANK('OITM_MASTER'[U_LBS_QCQTAllergies]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCSTorage]),ISBLANK('OITM_MASTER'[U_LBS_QCStorage]))&&OR(ISEMPTY('OITM_MASTER'[U_LBS_QCSubstance]),ISBLANK('OITM_MASTER'[U_LBS_QCSubstance])),"NOT FILLED","FILLED"))
Tutorial Requests

4 Replies

  • Dataanalyst2's avatar
    Dataanalyst2
    Frequent Visitor
    5 years ago

    Fowmy  Thank you so much for your help and suggestion. 

    Thou this method doesn't seem to be able to address to what I need.

     

    But I have tried again with a simplier syntax by removing the SUM and ISEMPTY functions on a New Column instead of New Measure, it worked fine. 

     

    Again thanks for trying to help and I have learnt sth new on pivoting column at least! 

     

    Best regards

     

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    I agree you should unpivot your table.  If you really can't do that, you can use this approach with a DAX column to check for all blank values.

     

     

    Pat

     

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Dataanalyst2 

     

    Can you share your solution? You can post it and accept it as solution then. This will help other community members who have similar questions. Thank you. ğŸ˜‰

     

    Best Regards,
    Community Support Team _ Jing