Fill in not existing values

[whereismydata]

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:

Taskname	2019-01-01	2019-01-02	2019-01-03	2019-01-04
Task A	X	X		Y
Task B	Y		Y	X

What I want:

Taskname	2019-01-01	2019-01-02	2019-01-03	2019-01-04
Task A	X	X	no task scheduled	Y
Task B	Y	no task scheduled	Y	X

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

[AlB]

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]

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,

[AlB]

hmmm.  I think it won't be that simple. I'd forgotten I came across this issue some time back. Check this out:

https://community.powerbi.com/t5/Desktop/Force-displaying-of-measure/td-p/659059

[whereismydata · Accepted solution]

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: