Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated Columns and Measure

Hi,   I'm trying to create a measure that looks at columns Journey Alias and Stop Comp over 5 mins and returns either Compliant or Not Compliant for a specific Journey Alias.   Eg. 101191351 shou...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi Anonymous ，

    You could  try the following measure:

    test =
    VAR not_compliant =
        CALCULATE (
            COUNT ( 'Table'[Stop Comp over 5 mins] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Journey Alias] = MAX ( 'Table'[Journey Alias] )
                    && 'Table'[Stop Comp over 5 mins] = "Not Compliant"
            )
        )
    VAR at_all =
        IF ( not_compliant >= 1, "Not Compliant", "Compliant" )
    RETURN
        at_all

     

     

    Finally you will see:

     

     

    If this is not what you want, please describe what you want, thank you!

     

     

    Best Regards,

    Lucien

     

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , Try a measure like

if(isblank(calcuate(countx(filter(Table, containsstring(Table[Stop over 5 Min], "Not Complaint")),Table[Journey Alias]), allexcept(Table, Table[Journey Alias]))),"Complaint","Not Complaint")

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    this measure seems to inflate the number of actual rows in the table, there's only meant to be 40 rows and this just keeps going when I scroll down and all rows turn into Not Compliant, unless I filter the Journey Aliases one by one then the measure works

     

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      Anonymous ,

      Try this one

      if(isblank(calculate(countx(filter(Table, containsstring(Table[Stop over 5 Min], "Not Complaint")),Table[Journey Alias]), filter(allselected(Table), Table[Journey Alias] =max(Table[Journey Alias])))),"Complaint","Not Complaint")