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

[jimmyhua]

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

    )

[Rupak_bi]

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

[BeaBF]

@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

[jimmyhua]

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.

[Anonymous]

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

[jimmyhua]

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

[Anonymous · Accepted solution]

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