Forum Discussion

akim_no's avatar
akim_no
Helper III
4 months ago
Solved

Displaying Table Fields as Rows in DirectQuery

  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 c...
Need Help
  • Dyuti_M9's avatar
    Dyuti_M9
    4 months ago

    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's avatar
    Dyuti_M9
    4 months ago

    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!

     



akim_no's avatar
akim_no
Helper III
4 months ago

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's avatar
Dyuti_M9
Frequent Visitor
4 months ago

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.