topic Re: Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division in DAX Commands and Tips

Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division

jimmyhua — Fri, 08 Nov 2024 16:49:21 GMT

Hi All,

I try to create a dax to pull top 10 projects with backlogs by division and by month. below is the dax. I got an error message saying cannot convert Division name to T/F logic. what could be wrong?

VAR DivisionMonthTable =

ADDCOLUMNS(

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[Div Name]),KEEPFILTERS( PJ401[Date]))

"RankByBL",

RANKX(

All(PJ401[Project ID]),

[Backlog],

DESC,

Dense

)

RETURN

FILTER(

DivisionMonthTable,

[RankByBL] <= 10

)

Re: Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division

BeaBF — Fri, 08 Nov 2024 18:08:47 GMT

jimmyhua Hi! Try with:

VAR DivisionMonthTable =
ADDCOLUMNS(
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])
)
),
"RankByBL",
RANKX(
FILTER(
ALL(PJ401),
PJ401[Div Name] = EARLIER(PJ401[Div Name]) && PJ401[Date] = EARLIER(PJ401[Date])
),
[Backlog],
,
DESC,
Dense
)
)
RETURN
FILTER(
DivisionMonthTable,
[RankByBL] <= 10
)

BBF

Re: Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division

jimmyhua — Fri, 08 Nov 2024 18:20:10 GMT

Hi BeaBF,

Thank you very much. I tried. It got rid of the error. but the ranking seems off. all projects get a ranking of 1 and the result is the Sum of all projects. do i need to put in a keepfilter somewhere? Really appreciated your help.

Re: Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division

Rupak_bi — Mon, 11 Nov 2024 06:40:24 GMT

HI jimmyhua
please share sample data and desired output for better understanding and tested solution.

Re: Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division

Anonymous — Mon, 11 Nov 2024 09:05:43 GMT

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

Re: Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division

jimmyhua — Mon, 11 Nov 2024 12:14:35 GMT

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.

Re: Error: cannot convert value 'CSS' of type Text to type True/False in which CSS is a division

Anonymous — Tue, 12 Nov 2024 02:56:06 GMT

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

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