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 e...
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

        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