Forum Discussion

TinaB's avatar
TinaB
Regular Visitor
6 years ago
Solved

Live data source, calculate error results for multiple columns, if any fail, record is "bad"

I have a data source connected with direct query from oracle database. I want to determine if the values in three different fields contain errors to calculate percentage of conformance to process exe...
Need Help
Tutorial Requests
  • TinaB's avatar
    TinaB
    6 years ago

    Thanks for the information, it was helpful in finding the solution. What I ran into was that comparing integers and text cannot be performed in the same arguements. One of the fields should have contained numbers and the other two were text. So I had to create new columns for each field and determing good/bad by column. Then add another column to review the results of the other 3 columns to determine overall conformance. Next, I created two measures, one to calculate the good records and the second to calculate the percentage.

     

    Solution:

    Create Columns:

    Column1 = IF(Table[FieldX] = 0 || Table[FieldX] > 43200,"Bad","Good")

     

    Column2 = IF(ISBLANK(Table[FieldY])|| CONTAINSSTRING(Table[FieldY], "=)") || CONTAINSSTRING(Table[FieldY], "n/a") ,"Bad","Good")

     

    Column3 = IF(ISBLANK(Table[Field Z])|| CONTAINSSTRING(Table[Field Z], "=)") || CONTAINSSTRING(Table[Field Z], "n/a") ,"Bad","Good")

     

    Column4 = IF(Table[Column1] = "Bad" || Table[Column2] = "Bad" || Table[Column3] = "Bad","Bad","Good")

     

    Create Measures:

    GOOD_RECORDS = CALCULATE(COUNTA('Table'[Field]), 'Table'[Column4] IN { "Good" })

    PERCENT_GOOD = DIVIDE([GOOD_RECORDS], COUNTA('Table'[Field]))

     

    Then use these new columns and measures in your visuals.

edhans's avatar
edhans
Community Champion
6 years ago

You can do this in Power Query with the if this = that or this > the other or this < something then DoSomething else DoSomethingElse.

 

  • But if you want to use DAX, don't use AND or OR. Use && or ||.
    • && is for AND comparisons
    • || is for OR comparisons

So 

 

IF(
    Table[field] = "X"
      || Table[Field2] = "Y"
      || Table[Field3] = "Z"
etc.

 

With && and || you can have unlimited comparisons.

TinaB's avatar
TinaB
Regular Visitor
6 years ago

Thanks for the information, it was helpful in finding the solution. What I ran into was that comparing integers and text cannot be performed in the same arguements. One of the fields should have contained numbers and the other two were text. So I had to create new columns for each field and determing good/bad by column. Then add another column to review the results of the other 3 columns to determine overall conformance. Next, I created two measures, one to calculate the good records and the second to calculate the percentage.

 

Solution:

Create Columns:

Column1 = IF(Table[FieldX] = 0 || Table[FieldX] > 43200,"Bad","Good")

 

Column2 = IF(ISBLANK(Table[FieldY])|| CONTAINSSTRING(Table[FieldY], "=)") || CONTAINSSTRING(Table[FieldY], "n/a") ,"Bad","Good")

 

Column3 = IF(ISBLANK(Table[Field Z])|| CONTAINSSTRING(Table[Field Z], "=)") || CONTAINSSTRING(Table[Field Z], "n/a") ,"Bad","Good")

 

Column4 = IF(Table[Column1] = "Bad" || Table[Column2] = "Bad" || Table[Column3] = "Bad","Bad","Good")

 

Create Measures:

GOOD_RECORDS = CALCULATE(COUNTA('Table'[Field]), 'Table'[Column4] IN { "Good" })

PERCENT_GOOD = DIVIDE([GOOD_RECORDS], COUNTA('Table'[Field]))

 

Then use these new columns and measures in your visuals.