Task Completed Daily

[snoozee]

Hi

I'm trying to work out DAX for the following and I think I have to create a new table ?

I need to know whether a certain task has been completed on daily basis by every person, so in the example below I want to know if Task A has been completed on a daily basis by Joe, Mary & Sam

Name	Date	Task
Joe	1/01/2022	A
Mary	1/01/2022	B
Sam	1/01/2022	A
Joe	2/01/2022	B
Mary	2/01/2022	A
Sam	3/01/2022	A
Joe	3/01/2022	B

I'm thinking that i would need to create this in a new table as it would end up looking something like this

Name	Date	Task	Completed
Joe	1/01/2022	A	Y
Sam	1/01/2022	A	Y
Mary	1/01/2022		N
Sam	2/01/2022		N
Mary	2/01/2022	A	Y
Joe	2/01/2022		N
Sam	3/01/2022	A	Y
Mary	3/01/2022		N
Joe	3/01/2022		N

Any help would be greatly appreciated 🙂

[v-kkf-msft]

Hi @snoozee ,

Please try the calaulated table.

NewTable = 
ADDCOLUMNS (
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'Table'[Name] ), VALUES ( 'Table'[Date] ) ),
        "Task",
            CALCULATE (
                MAX ( 'Table'[Task] ),
                FILTER (
                    'Table',
                    'Table'[Name] = EARLIER ( [Name] )
                        && 'Table'[Date] = EARLIER ( [Date] )
                )
            )
    ),
    "Completed", IF ( [Task] = "A", "Y", "N" )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[snoozee]

Hi

I'm struggling to get this to work and unfortunately can't open your attachment as I can't upgrade my May 2021 version of Power BI as if I do I can't save to our server 😞

Table = 
ADDCOLUMNS(
ADDCOLUMNS(
CROSSJOIN( VALUES('L_Teller_BalanceTransaction'[BranchCode]), VALUES('L_Teller_BalanceTransaction'[BalanceDatetime].[Date])),
"BalanceType",
CALCULATE(
MAX('L_Teller_BalanceTransaction'[BalanceType]),
FILTER('L_Teller_BalanceTransaction',
'L_Teller_BalanceTransaction'[BranchCode]=EARLIER('L_Teller_BalanceTransaction'[BranchCode]) && 'L_Teller_BalanceTransaction'[BalanceDatetime].[Date] =EARLIER('L_Teller_BalanceTransaction'[BalanceDatetime].[Date])
))),
"Completed",IF([BalanceType] = "BULK","Y","N" ))

It's not showing the correct results

The completed column in the image below is correct at "N" based on the BalanceType, however when I filter the date by 8th March BalanceType BULK isn't appearing, yet branchcode 00085014 on the 8th March did complete a BULK balance so can't work out why it's not appearing 😞

[snoozee]

@amitchandak  Unfortunately I can't open the file as i'm using an earlier version of Power Bi (May 2021). I had to roll back to this verson as otherwise I was unable to save to our Report Server.

[johnt75]

You will need a date table and also a table for the task dimension, which you could create as

Task Dimension = ALLNOBLANKROW('Table'[Task])

create a one-to-many relationship from the task dimension to the fact table, and from the date table to the fact table and create a measure like

Task Completed =
VAR currentName =
    SELECTEDVALUE ( 'Table'[Name] )
VAR currentDate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR currentTask =
    SELECTEDVALUE ( 'Task Dimension'[Task] )
RETURN
    IF (
        ISEMPTY (
            TREATAS (
                { ( currentName, currentDate, currentTask ) },
                'Table'[Name],
                'Table'[Date],
                'Table'[Task]
            )
        ),
        "No",
        "Yes"
    )

[snoozee]

Hi

I've given this a whirl yet it's not working 😞

I created my dimension table

Created the relationships

Created the measure

Used the measure in my table

The Task Completed column only displays No, branchCode 00010006 as an example should be showing Yes for the 4th May and for ones that should be No they aren't even displaying, the table is empty when i filter by the relevant branchcode

Where am I going wrong 🙂

[v-kkf-msft]

Hi @snoozee ，

Please open a blank query and paste the code in Power Query Editor.

let
    Source = List.Distinct( L_Teller_BalanceTransaction[BalanceDatetime] ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "BalanceDatetime"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "BranchCode", each List.Distinct(L_Teller_BalanceTransaction[BranchCode])),
    #"Expanded BranchCode" = Table.ExpandListColumn(#"Added Custom", "BranchCode"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded BranchCode", {"BalanceDatetime", "BranchCode"}, L_Teller_BalanceTransaction, {"BalanceDatetime", "BranchCode"}, "L_Teller_BalanceTransaction", JoinKind.LeftOuter),
    #"Expanded L_Teller_BalanceTransaction" = Table.ExpandTableColumn(#"Merged Queries", "L_Teller_BalanceTransaction", {"BalanceType"}, {"BalanceType"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded L_Teller_BalanceTransaction", "Completed", each if [BalanceType] = "BULK" then "Y" else "N"),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"BalanceDatetime", Order.Ascending}, {"BranchCode", Order.Ascending}})
in
    #"Sorted Rows"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[snoozee]

Hi

Thanks for this, however it's not working as it's only showing me Yes results for the BULK task when i know that some should be No.

This is what i'm trying to achieve

I need to be able to see whether a branch (BranchCode) has completed a balance (BULK) on a daily basis, so if they have completed it then the completed column would show Yes, if not it would show No.

Hope that makes sense

[amitchandak]

@snoozee , refer if the attached file can help

[snoozee]

I can't open this file as i use Power BI (May 2021) version. I can't upgrade either as otherwise i can't save to our report server 😞

Could you create it in an earlier version for me ?