Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @PowerBIsv,
SUMMARIZE function will generate a table, you can't direct use this function in calculated column.
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
Hi @PowerBIsv,
SUMMARIZE function will generate a table, you can't direct use this function in calculated column.
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
Absolutely perfect!
Thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |