Help with Dax.

[Tarun_B]

Hello All,

I need help with a DAX formula (or using variables) to solve a data quality scoring issue. Here's the situation:

• I have a static table called "Parent Table" that contains:

  • Table Name
  • Parent Table Name

• I also have a query from a database that returns data with:

  • Table Name (sometimes it's the original name, sometimes it's a mapped/alias name)

Mapped names

Original names 

I want to:

• Average the Data Quality Scores for each logical table (regardless of whether it's referred to by its original or mapped name).
• Compare that average to a threshold (from a threshold table).
• Return "Green" if the average score meets/exceeds the threshold, otherwise "Red".

Threshold 

Calcualation for quality scores. 

[v-venuppu]

Hi @Tarun_B ,

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.

[v-venuppu]

Hi @Tarun_B ,

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.

[v-venuppu]

Hi @Tarun_B ,

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.

[v-venuppu · Accepted solution]

Hi @Tarun_B ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @burakkaragoz for the prompt response.

I have tried replicating the scenario using sample data.Please go through the attached PBIX file for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Tarun_B]

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."

[Tarun_B]

The mapped table contains unique values created through merge queries in Mquery.

Requirement: When the average quality score is 92.30% (highlighted in pink) and does not meet the threshold, it should be flagged for debugging. The threshold indicator should be red instead of green.

[burakkaragoz]

Hi @Tarun_B ,

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:

dax

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:

dax

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:

dax

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!