Forum Discussion

donkyhonky_99's avatar
donkyhonky_99
New Member
2 years ago
Solved

Evaluating table history

General Question: Is it possible to work with history tables in Power BI effectively?
Example Problem:
I have a predefined task, which has 10 process steps. Everytime a process step of a task is completed, it gets an entry in my table with the TaskID, the step (1-10) which was completed,  and the date it was completed and if the task had an Error or not. If there was an error the Task fails, and if not the task moves to the next step.
Consider the following example table: 
TaskID, process step, complete_date, Error,
3,            step2,          7/20/2024,       True
3,            step1,          7/14/2024,       False
5,            step6,          7/23/2024,       True
5,            step5,          7/23/2024,       False
5,            step4,          7/11/2024,       False
...
[The real table has 100.000+ rows]
Now I need to make a historical evaluation. For every day I need to know exactly how many task are in which step, or stuck with an error and so on.
Let say I take the 7/20/2024, then I know taskID 3 has completed step2 but with an error, taskID 5 has completed step4 with no error. 
For my general table I will get something like 500 tasks in step 1, 600 in step 2, and so on

I want to display general information about each day using a date slicer, on how things have looked for that specific day (a look in the past), I want to display how many where in each step, how many task had an error and so on.

In Power BI I know I can evaluate these using a measure, I did this. But the problem is from a measure I cannot use this information in any visualization as a legend. 
I cannot create a pie chart using categorization of error/ no error as a legend and so on, because this calculation uses a measure.

In general it is hard for me to deal with a historical evaluation, based on a history table and then evaluating the results for a specific day in the past.

Is there a way to solve this problem, maybe not using measures? Or a general guideline where someone dealt with the same problem? I did not find anything related to this topic yet 😕

Thank you in advance

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, donkyhonky_99 

    You can create calculated columns in Power BI to categorize tasks based on their status on a specific date. This allows you to use these columns directly in your visualizations.

    Based on your information, I create a sample table:

     

    Create a date table and make sure you have a comprehensive date table in your model.

    Date = CALENDAR(MIN('Table'[Complete Date]),MAX('Table'[Complete Date]))

     

    Then create a calculated column:

    TaskStatus = 
VAR CurrentDate = 'Date'[Date]
RETURN
    CALCULATE(
        MAX('Table'[process step]),
        FILTER(
            'Table',
            'Table'[Complete Date] <= CurrentDate
        )
    )

     

    TaskErrorStatus = 
IF(
    'Table'[Error] = TRUE,
    "Error",
    "No Error"
)

     

    Here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, donkyhonky_99 

    You can create calculated columns in Power BI to categorize tasks based on their status on a specific date. This allows you to use these columns directly in your visualizations.

    Based on your information, I create a sample table:

     

    Create a date table and make sure you have a comprehensive date table in your model.

    Date = CALENDAR(MIN('Table'[Complete Date]),MAX('Table'[Complete Date]))

     

    Then create a calculated column:

    TaskStatus = 
VAR CurrentDate = 'Date'[Date]
RETURN
    CALCULATE(
        MAX('Table'[process step]),
        FILTER(
            'Table',
            'Table'[Complete Date] <= CurrentDate
        )
    )

     

    TaskErrorStatus = 
IF(
    'Table'[Error] = TRUE,
    "Error",
    "No Error"
)

     

    Here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Icon for Super User rankSuper User
    2 years ago

    Sounds like you just need to count how many rows have error ie a measure

    Then something like a piechart that shows the percentage of steps that has error vs no errors based on a selected date.

    Or a table that has Date, TaskID, and Step. And just filter the visual to Error is TRUE. Then you get a list of days with failed steps