Create holiday chart - The expression refers to multiple columns...

[PowerBIsv]

Hello DAX-people!

I want to create a report that show who(how many) is on holiday in a "Clustred column chart".

The problem: I keep getting "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".
I have tried(done the exact same thing) some similar examples that seems to work but I must be doing something wrong.
One of the examples i tried: https://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggrega...

I have two tables:
1. Date-table "Calendar = CALENDAR (DATE(2017;1;1); DATE(2017;12;1))"
2. Holiday Schedule-table
 Name (ex David)
 Qualification (ex A)
 Start Date (ex 2017-11-01)
 End Date (ex 2017-11-15)
 
 There is no relation between the tables.
 
 Tried this Calculated column(and Meassure but I think Calculated Column is the way to go, right?)
 
 Holiday Schedule =
SUMMARIZE (
    GENERATE (
        HolidayTable;
        CALCULATETABLE (
            VALUES ( 'Calendar'[Date] );
            DATESBETWEEN (
                'Calendar'[Date];
                HolidayTable[Start Date];
                HolidayTable[End Date]
            )
        )
    );
    HolidayTable[Name];
    'Calendar'[Date];
    HolidayTable[Qualification]
)

I would really appreciate some guidance.

What i hope to accomplish.

[Anonymous · Accepted solution]

Hi @PowerBIsv,

SUMMARIZE function will generate a table, you can't direct use this function in calculated column.

SUMMARIZE Function (DAX)

Returns a summary table for the requested totals over a set of groups.

For your requirement, I'd like to suggest you to create the expand table to stored the detail holiday range, then create relationship with calendar table and build visual on these columns.

Table formula:

Holiday Schedule = 
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( HolidayTable, 'Calendar' ),
            [Date] >= [Start Date]
                && [Date] <= [End Date]
        ),
        [Name],
        [Date],
        [Qualification]
    )

Result:

Regards,

Xiaoxin Sheng

[PowerBIsv]

Absolutely perfect!

Thank you so much!