Help with Dax.

Hi Anonymous ,

Great question! Here’s how you can approach your data quality scoring problem in DAX, based on your scenario:

1. Normalize Table Names

To ensure you’re grouping/averaging by the logical table (regardless of whether it’s the mapped or original name), create a calculated column in your data table that resolves to the “Parent Table Name” for each row.

Suppose:

• “Parent Table” has columns: [Mapped Table], [Parent Table Name]
• Data table has [Table Name] (could be mapped or original)

You can add a calculated column in your data table:

ResolvedTableName =
VAR FoundMapped =
    LOOKUPVALUE(
        'Parent Table'[Parent Table Name],
        'Parent Table'[Mapped Table], [Table Name]
    )
RETURN
    IF(
        NOT(ISBLANK(FoundMapped)),
        FoundMapped,
        [Table Name]   // fallback to original
    )

2. Average Data Quality Score by Logical Table

Create a measure:

AvgQualityScore =
AVERAGEX(
    VALUES('DataTable'[ResolvedTableName]),
    CALCULATE(AVERAGE('DataTable'[DataQualityScore]))
)

(Adjust for your actual field names.)

3. Compare to Threshold

Assuming you have a threshold table with columns: [Parent Table Name], [Threshold Score]

Create a measure for color indicator:

DQ_Status =
VAR TableName = SELECTEDVALUE('DataTable'[ResolvedTableName])
VAR AvgScore = CALCULATE(AVERAGE('DataTable'[DataQualityScore]), 'DataTable'[ResolvedTableName] = TableName)
VAR Threshold = LOOKUPVALUE('ThresholdTable'[Threshold Score], 'ThresholdTable'[Parent Table Name], TableName)
RETURN
    IF(AvgScore >= Threshold, "Green", "Red")

4. Show the Status in Your Visual

• Use [ResolvedTableName] as your grouping field.
• Show [AvgQualityScore] and [DQ_Status] in your table or matrix visual.

Summary:

• Use LOOKUPVALUE to resolve all names to their “parent” logical name.
• Use AVERAGE/AVERAGEX for quality scores.
• Use LOOKUPVALUE again to pull in threshold and compare for your status.

If you need a sample PBIX or run into issues with the DAX, let me know the exact table/column names and I can help you write out the full formulas!

Hope this helps!