Forum Discussion

whereismydata's avatar
whereismydata
Icon for Resolver IV rankResolver IV
7 years ago
Solved

Fill in not existing values

Hi,

 

I'm trying to recreate a table which I had build for reporting server recently. In RS it was easy to fill in 'missing' values with an expression (if cell is empty, then write "no task scheduled" a fill with grey color). Missing in this context means, they are supposed to because on this date no task was schedueled.

 

table:

 

Taskname => text, Name of the task

Taskdate => date, Date of scheduled task (they do not occur on every day)

Person => text, Person who reviews the task

Errorcode => int, id of error I use for conditional formatting

 

Taskname|Taskdate|Person|Errorcode

TaskA|2019-01-01|X|1

TaskA|2019-01-02|X|0

TaskA|2019-01-04|Y|1

TaskB|2019-01-01|Y|1

TaskB|2019-01-03|Y|1

TaskB|2019-01-04|X|1

 

 Table visualisation looks like this:

Taskname2019-01-012019-01-022019-01-032019-01-04
Task AXX Y
Task BY YX

 

What I want:

Taskname2019-01-012019-01-022019-01-032019-01-04
Task AXXno task scheduledY
Task BYno task scheduledYX

 

Is there a way I can generate the "no task scheduled" with an DAX query? Or do I have to generate the missing values in a table?

 

ps: this is dynamic, there is no rule when a task is not scheduled, also there are +/- 20 tasks each day so a manual solution is not so good :)

 

 

thank you very much for your help

Need Help
  • whereismydata's avatar
    whereismydata
    7 years ago

    Hi AlB ,

     

    thank you for your reply. I took a look into the thread and luckily it pointed me to a possible solution, although not with DAX. 

     

    let
    Source = Tasks,
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[TaskName]), "TaskName", "Person"),
    #"Replaced Value" = Table.TransformColumns(#"Pivoted Column",{},(x) => Replacer.ReplaceValue(x,null,"not scheduled")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"TaskDate"}, "Task", "Person"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Person", type text}, {"TaskDate", type date}})
in
    #"Changed Type"

    Source:

     

     

    First I pivot on the taskName column so that all the task rows get pivoted into columns. As value I take person and as aggregate function 'Don't aggregate'. This creates nulls where no task is scheduled.

     

     

     

    Next I replace all nulls with my custom text (eg 'not scheduled') with

    Table.TransformColumns(#"Pivoted Column",{},(x) => Replacer.ReplaceValue(x,null,"not scheduled"))

    Then I unpivot TaskA and TaskB so that I get my Task and Person columns back. but this time with my added values.

     

     

    This creates my desired output, unfortunately not with dax, but at least I did not have to enter the values in the database.

     

    Result:

     

     

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi whereismydata 

    Try this measure in a matrix visual, with  TaskName and TaskDate in rows, columns of the visual:

     

    Measure =
VAR NumAppearances_ =
    COUNTROWS ( Table1[Person] )
RETURN
    SWITCH (
        NumAppearances_,
        1, DISTINCT ( Table1[Person] ),
        0, "no task scheduled",
        BLANK ()
    )

     

    • whereismydata's avatar
      whereismydata
      Icon for Resolver IV rankResolver IV
      7 years ago

      Hi AlB 

       

      thank you for taking time to find a solution for my problem.

       

      I tried to create the measure, but got the error that the syntax for 'distinct' is incorrect:

       

      The syntax for 'DISTINCT' is incorrect. (DAX(VAR NumAppearances_ = COUNTROWS ( taskTable[Person] )RETURN SWITCH ( NumAppearances_, 1. DISTINCT ( taskTable[Person] ), 0. "no task scheduled", BLANK () ))).

       

      Measure = VAR NumAppearances_ =
    COUNTROWS (taskTable[Person] )
RETURN
    SWITCH (
        NumAppearances_;
        1, Distinct ( taskTable[Person] );
        0, "no task scheduled";
        BLANK ()
    )

       

       

      EDIT:

       

      I got the formula working. Countrows only accepts a table as value:

      Measure2 = 
VAR NumAppearances_ =
    COUNTROWS ( Tasks )
RETURN
    SWITCH (
        NumAppearances_;
        1; DISTINCT ( Tasks[Person] );
        0; "no task scheduled";
        BLANK ()
    )

      Also there where some issues with "," & ";" maybe due to localization.

       

      Unfortunately the issue still exits. Attached a screenshot.

       

       

       

      Thank you for your help. It is highly appreciated.

       

      best,