Forum Discussion

E12345's avatar
E12345
Resolver II
1 year ago
Solved

Dax measure needed

Hi! I need help formatting a text column. It seems so easy to do it in SQL using string functions, but I need help doing it using DAX: I have a column that has two problems - each value is a list ...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    I realized you wanted DAX so here they are

    CapitalizedFirstTwoCharacters CalcColumn = 
VAR CountOfComma =
    LEN ( 'Table'[MyColumn] ) - LEN ( SUBSTITUTE ( 'Table'[MyColumn], ",", "" ) )
VAR Numbers =
    GENERATESERIES ( 1, 2, 1 )
VAR Substituted =
    SUBSTITUTE ( 'Table'[MyColumn], ",", "|" )
VAR _Names =
    ADDCOLUMNS (
        Numbers,
        "@name",
            VAR _item =
                PATHITEM ( Substituted, [Value] )
            VAR _itemLength =
                LEN ( _item )
            RETURN
                UPPER ( LEFT ( _item, 2 ) )
                    & LOWER ( RIGHT ( _item, _itemLength - 2 ) )
    )
RETURN
    CONCATENATEX ( _Names, [@name], ", " )

     

    CapitalizedFirstTwoCharacters Measure = 
VAR _Value = MAX ( 'Table'[MyColumn] )
VAR CountOfComma =
    LEN ( _Value ) - LEN ( SUBSTITUTE (_Value, ",", "" ) )
VAR Numbers =
    GENERATESERIES ( 1, CountOfComma, 1 )
VAR Substituted =
    SUBSTITUTE (_Value, ",", "|" )
VAR _Names =
    ADDCOLUMNS (
        Numbers,
        "@name",
            VAR _item =
                PATHITEM ( Substituted, [Value] )
            VAR _itemLength =
                LEN ( _item )
            RETURN
                UPPER ( LEFT ( _item, 2 ) )
                    & LOWER ( RIGHT ( _item, _itemLength - 2 ) )
    )
RETURN
    CONCATENATEX ( _Names, [@name], ", " )

     

E12345's avatar
E12345
Resolver II
1 year ago

Thank you so much, but you are using M language, and I need a DAX measure to put in Power BI Desktop. We use live connection so I do not have access to Power Query/M language. 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hello,danextian ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.


Hi,E12345 .I am glad to help you.
I note that danextian has already provided a solution case for dax code.
However, since you are using live connections as your data connection mode.There are some DAX functions and capabilities that may be limited.Live Connections mode is primarily used for connecting to existing Power BI datasets or Analysis Services models, rather than importing data directly. As a result, some data manipulations and calculations may not be as flexible as in Import mode.
This is related to the version of your model.
You can try testing this measure in Power BI Desktop using Live Connections mode to see if it works properly.
As you mentioned, in Live Connections mode, users usually don't have access to modify the data model. Therefore, moving some of the calculations to the data source for preprocessing is also a better solution. This ensures that when you use Live Connections in Power BI, the data has been processed the way it needs to be.
Provided you have the authority to modify that AS model.
This is a limitation of the Live connections connection model.
Solved: Re: Live connection limitations - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

  • E12345's avatar
    E12345
    Resolver II
    1 year ago

    Thank you for the suggestion. I do npt have access to the source data/data model, so I only rely on Dax measures at the Power BI Desktop side.