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

Need Help

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:

whereismydata

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