Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

filter and search

'TableName'[ColumnA] is of datatype Text with values such as
1422.02.02
200500
50100
6401.03.03
...

The below DAX does not show any values eventhough I see texts starting with the below values i.e. 6 or 7 or 8 or 9
CALCULATE(
[MeasureB],
LEFT('TableName'[ColumnA], 1) = "6"
|| LEFT('TableName'[ColumnA], 1) = "7"
|| LEFT('TableName'[ColumnA], 1) = "8"
|| LEFT('TableName'[ColumnA], 1) = "9"
)

The below DAX fails with error: cannot convert value '' of type Text to type Number

CALCULATE(
[MeasureB,
VALUE(LEFT('TableName'[ColumnA], 1)) >= 6
)

 

Any suggestions please?

9 REPLIES 9
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

By my test with your formula, I cannot reproduce your issue.

Capture.PNG

You could have a reference of my attachment.

In addition, if it is convenient, could you please share your data sample as table format and your desired output so that we could help further on it?

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
parry2k
Super User
Super User

@Anonymous what is measureb , then 2nd dax expression will fail if left of a string is blank and first one should work



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

measureB is number

@Anonymous understood but what is the expression? Is it from same table? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

I seem to be getting closer to my solution, however, the measureC column shows all values the same which is the sum of the MeasureB with ColumnA starting with 1 or 5

Question, all data is from one table

Not sure why all rows in MeasureC are repeated with the sum of MeasureB

This is my DAX

CALCULATE(
[MeasureB]
, not ISBLANK('TableName'[ColumnA]) && SEARCH(".", 'TableName'[ColumnA],,0) = 0
&& LEFT('TableName'[ColumnA], 1) = "1"
||LEFT('TableName'[ColumnA], 1)= "5"
|| LEFT('TableName'[ColumnA], 1)= "6"
|| LEFT('TableName'[ColumnA], 1) = "7"
|| LEFT('TableName'[ColumnA], 1) = "8"
|| LEFT('TableName'[ColumnA], 1) = "9"
)

@Anonymous not going into your measure, add following 

 

CALCULATE(
[MeasureB]
, 
KEEPFILTERS(
not ISBLANK('TableName'[ColumnA]) && SEARCH(".", 'TableName'[ColumnA],,0) = 0
&& LEFT('TableName'[ColumnA], 1) = "1"
||LEFT('TableName'[ColumnA], 1)= "5"
|| LEFT('TableName'[ColumnA], 1)= "6"
|| LEFT('TableName'[ColumnA], 1) = "7"
|| LEFT('TableName'[ColumnA], 1) = "8"
|| LEFT('TableName'[ColumnA], 1) = "9"
)
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

using your DAX but this time nothing is shown into the column

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Anonymous i would recommend to share pbix wiht sample data to look into it. information you provided is hard to work with until someone else can assist you wiht this.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.