Displaying Table Fields as Rows in DirectQuery

[akim_no]

I am currently facing an issue in Power BI while trying to build a dynamic data completeness table from a wide table structure. My goal is to display each field as a row, along with its value, a completeness indicator (blank or not), and a completion rate. This table must be dynamic and respond to a drill-through filter (e.g., filtered by a unique identifier).

⚠️ Important context: I am working in DirectQuery mode, so transformations in Power Query (such as unpivoting) are limited or not preferred due to performance constraints.

🔹 Input data example

Source table (FactData):

 

RecordID | Field_A | Field_B | Field_C | Field_D
------------------------------------------------
1001       | Yes        | No       | (null)      | Yes

 

 

🔹 Expected output

 

IsBlank | FieldName | Value | Completion Rate
------------------------------------------------
0          | Field_A       | Yes       | 100%
0          | Field_B       | No        | 100%
1          | Field_C       | (empty) | 0%
0          | Field_D      | Yes         | 100%

👉 With a global completion indicator:
3 out of 4 fields filled → 75%

Technical constraints

• No global transformation (pivot/unpivot) in Power Query
• Requirement limited to a specific drill-through page
• Dynamic filtering based on RecordID

  Objective

  Looking for a robust approach to:

  • dynamically transform columns into rows
  • correctly retrieve values based on filter context
  • calculate completeness indicators (per field and global)
  • fully compatible with DirectQuery

[akim_no]

Now, I’m wondering if it’s possible to create a general measure to calculate a completion rate. The idea would be for this measure to automatically detect which columns are being used and which are empty, in order to calculate an overall completion percentage, instead of checking each column individually and using a fixed calculation. I’m looking for something quick and dynamic; otherwise, would it be necessary to use configuration tables or a similar approach to make it more flexible and maintainable?

[Dyuti_M9]

Hi @akim_no ,
The earlier approach was based on a Static values (such as Yes/No). However, to make the solution more flexible, here’s a dynamic approach to calculate the completion rate.
Based on your reference table, I created an unpivoted table named "Post_ETL", where all fields are transformed into rows.

In this structure, the "Value" column can contain multiple types of entries and is no longer limited to specific values like Yes or No.
Measure to calculate completion count

How the measure works:

• It filters only those rows where the Value is not blank
• Then it counts the total number of actual entries present for each record
• This ensures the count dynamically updates based on the underlying data

Measure to calculate completion rate%

For the completion rate:

• Use Actual_Entries as the numerator
• Use (Distinct count of Fields × Distinct count of Records) as the denominator
• Format as Percentage.

This calculation provides the overall completion rate, considering only non-blank entries and dynamically adjusting based on the data and applied filters.

Hope this helps!

[Dyuti_M9]

Hi @akim_no , when you are transforming your data using Dataflowgen 2 , the power query editor , you can add a conditional coloumn, the condition:
if Value contains "Yes" then 1
if Value contains "No" then 1
else 0

In your Power BI Report write a measure:

Completion Rate % =

DIVIDE(

    [Actual Entries],

    DISTINCTCOUNT('After ETL'[Field Name]) * DISTINCTCOUNT(Sheet1[Record_ID ]),

    0

)
This would give you the total completion rate for the entire data set and also change dynamically.

This is also suited for slicer selection and card visuals:

Hope this works.

[Dyuti_M9]

Right, @InsightsByV  this approach makes a lot of sense.

Performing the transformation at the source (e.g., using Dataflow Gen2) would definitely help reduce refresh overhead and improve overall report performance.

If we try to handle this within Power BI in DirectQuery mode, it would require creating a disconnected table and additional DAX logic such as Datatable and Switch , which can become difficult to maintain as the number of fields grows.
So pushing this logic upstream is a much more scalable and cleaner approach.

[InsightsByV]

Hi @akim_no ,

As you are in DirectQuery mode, I believe pushing the desired transformation at source level. I have come across similar issues where transformations were required. For which, we created DataFlow Gen2 in Fabric and used that table in Power BI.

This will keep your model light as well.