Forum Discussion
Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division
Hi jimmyhua ,
Sorry, I forgot to mention that “multiple columns cannot be scalar value” indicates that the measure returns a table. Normally a measure should return a single value.
'Filter' is a table function that returns a table:
RETURN FILTER ( DivisionMonthTable2, [RankByBL] <= 10 )If you want to dynamically rank by Department and Date, I think you need to create two slicers and use the Card visual.
This function can combine the table into a single value
CONCATENATEX function (DAX) - DAX | Microsoft Learn
VAR DivisionMonthTable = SUMMARIZE ( PJ401, PJ401[Div Name], PJ401[Date], PJ401[Project ID], PJ401[Project Name], "Backlog", CALCULATE ( SUMX ( PJ401, [CM CV] * [CV%] - [Inception-to-date Revenue] ), KEEPFILTERS ( PJ401[Date] ) ) ) VAR DivisionMonthTable2 = ADDCOLUMNS ( DivisionMonthTable, "RankByBL", RANKX ( DivisionMonthTable, [Backlog],, DESC, DENSE ) ) RETURN CONCATENATEX ( FILTER ( DivisionMonthTable2, [RankByBL] <= 10 ), [Div Name] & " " & [Date] & " " & [Project ID] & " " & [Project Name] & " " & [RankByBL], UNICHAR ( 10 ) )This result should change dynamically based on the slicer selection.
Something like this:
If you still have problems, please provide simple data or a .pbix file without sensitive data.
If you are unsure how to upload data please refer to
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Wenbin Zhou
Hi all,thanks for the quick reply, I'll add more.
Hi jimmyhua ,
Try this:
VAR DivisionMonthTable =
SUMMARIZE (
PJ401,
PJ401[Div Name],
PJ401[Date],
PJ401[Project ID],
PJ401[Project Name],
"Backlog",
CALCULATE (
SUMX ( PJ401, [CM CV] * [CV%] - [Inception-to-date Revenue] ),
KEEPFILTERS ( PJ401[Date] )
)
)
VAR DivisionMonthTable2 =
ADDCOLUMNS (
DivisionMonthTable,
"RankByBL", RANKX ( DivisionMonthTable, [Backlog],, DESC, DENSE )
)
RETURN
FILTER ( DivisionMonthTable2, [RankByBL] <= 10 )
Best Regards,
Wenbin Zhou
Anonymous really appreciated. your solution gives me a table of ranked top 10 by entire organization across all time period. I actually need top 10 result by month, by division. if no division is selected, then by the entire organization. I tried break the code in to different sections ( division or total organization). but then try to conditionally select which table to use based on external filter, it gives me an error message on multiple columns cannot be scalar value. apparently I cannot apply if or switch to a table. so I am still stuck. thanks again for helping.
Hi jimmyhua ,
Sorry, I forgot to mention that “multiple columns cannot be scalar value” indicates that the measure returns a table. Normally a measure should return a single value.
'Filter' is a table function that returns a table:
RETURN FILTER ( DivisionMonthTable2, [RankByBL] <= 10 )If you want to dynamically rank by Department and Date, I think you need to create two slicers and use the Card visual.
This function can combine the table into a single value
CONCATENATEX function (DAX) - DAX | Microsoft Learn
VAR DivisionMonthTable = SUMMARIZE ( PJ401, PJ401[Div Name], PJ401[Date], PJ401[Project ID], PJ401[Project Name], "Backlog", CALCULATE ( SUMX ( PJ401, [CM CV] * [CV%] - [Inception-to-date Revenue] ), KEEPFILTERS ( PJ401[Date] ) ) ) VAR DivisionMonthTable2 = ADDCOLUMNS ( DivisionMonthTable, "RankByBL", RANKX ( DivisionMonthTable, [Backlog],, DESC, DENSE ) ) RETURN CONCATENATEX ( FILTER ( DivisionMonthTable2, [RankByBL] <= 10 ), [Div Name] & " " & [Date] & " " & [Project ID] & " " & [Project Name] & " " & [RankByBL], UNICHAR ( 10 ) )This result should change dynamically based on the slicer selection.
Something like this:
If you still have problems, please provide simple data or a .pbix file without sensitive data.
If you are unsure how to upload data please refer to
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Wenbin Zhou