Forum Discussion

snoozee's avatar
snoozee
Regular Visitor
4 years ago

Task Completed Daily

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

 

NameDateTask
Joe1/01/2022A
Mary1/01/2022B
Sam1/01/2022A
Joe2/01/2022B
Mary2/01/2022A
Sam3/01/2022A
Joe3/01/2022B

 

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

 

NameDateTaskCompleted
Joe1/01/2022AY
Sam1/01/2022AY
Mary1/01/2022 N
Sam2/01/2022 N
Mary2/01/2022AY
Joe2/01/2022 N
Sam3/01/2022AY
Mary3/01/2022 N
Joe3/01/2022 N

 

Any help would be greatly appreciated 🙂

9 Replies

    • snoozee's avatar
      snoozee
      Regular Visitor
      4 years ago

      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 ?

       

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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's avatar
      snoozee
      Regular Visitor
      4 years ago

      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's avatar
        v-kkf-msft
        Community Support
        4 years ago

        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's avatar
    snoozee
    Regular Visitor
    4 years ago

    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.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
      snoozee
      Regular Visitor
      4 years ago

      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 😞