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!

I have tried all the solution provided by Chatgpt and Copilot. It give me the error 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Hi Anonymous ,

I want to check if you had the opportunity to review the information provided and resolve the issue.If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Hi Anonymous ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Hi Anonymous ,

I hope this information is helpful.If this answers your question, please accept it as a solution,so other community members with similar problems can find a solution faster.

Thank you.