Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 execution. Can we build a table that pulls in the data from the direct query using the record number (unique ID/master key) then create a query to return good/bad value for each record in three different columns? In excel, the formula I am trying to replicate is "=IF(OR(BG460<1,BR460=0,BV460=0),"Bad","Good")." What this does is looks at the value in the three cells within the same row as the record number and tells me if the record is bad or good. If any one of the conditions is met, the record fails. Then to calculate the conformance we divide the good records by the total records within a given date range.
There are a couple more errors within the cells that I would like to remove, like cells that contain "=)" or "?". Neither of which is a valid response within the column.
DAX OR function only allows two arguements, so I am stuck on how to resolve. Any suggestions would be greatly appreciated.
Solved! Go to Solution.
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.
You can do this in Power Query with the if this = that or this > the other or this < something then DoSomething else DoSomethingElse.
So
IF(
Table[field] = "X"
|| Table[Field2] = "Y"
|| Table[Field3] = "Z"
etc.
With && and || you can have unlimited comparisons.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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.