Forum Discussion

jimmyhua's avatar
jimmyhua
Helper I
1 year ago
Solved

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

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
    )
DAX help needed
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

     

6 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    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's avatar
      jimmyhua
      Helper I
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

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