topic Re: Receiving Cannot mix text with values error in DAX Commands and Tips

Receiving Cannot mix text with values error

LYorkToenniges — Thu, 25 Jul 2024 20:55:14 GMT

Here is the error I get:

Here is the formula (one of anyway) that I'm trying to get to work:

% Commitment =

IF(

MAX(DPP[Reason])="Leave"||MAX(DPP[Reason])="Project"||MAX(DPP[Reason])="PTO"||MAX(DPP[Reason])="Mentee",

"Excluded",

IF(

CALCULATE(

DIVIDE(

SUM('AC'[Produciton]),

SUM('DPP'[Daily Amount]),

BLANK()

DPP[Date]

)=BLANK(),

"Needs Review",

FORMAT(

CALCULATE(

DIVIDE(

SUM('AC'[Produciton]),

SUM('DPP'[Daily Amount]),

BLANK()

DPP[Date]

"Percent"

)

The goal is to have a column with text values for certain situations and numerical values formated as percentages for when there is a valid value to present. I've actually tried a few variations on this and an just burnt out. I know there has to be a way, I feel like I got this to work before, but wrapping the calculate in VALUE didn't help, and changing from "" to BLANK didn't help. Is it the IF statement as a whole?

I close my eyes and just see dax formulas scrolling now. Please help. Let me know if any more information is needed.

Re: Receiving Cannot mix text with values error

MattAllington — Thu, 25 Jul 2024 21:56:53 GMT

All I can see is that you seem to to have a date column as the single parameter in the calculate filter parameter position. I'm not sure what that's trying to do, but you can't do it that way. Maybe wrap the date column inside LASTDATE()

Re: Receiving Cannot mix text with values error

gmsamborn — Thu, 25 Jul 2024 22:00:18 GMT

Hi LYorkToenniges

I would like to help but I'm a little unclear about your requirements.

Can you provide the following?

1) Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

A .pbix file with sample data would be best.

Re: Receiving Cannot mix text with values error

LYorkToenniges — Fri, 26 Jul 2024 12:03:42 GMT

That actually works in other places I've used it. This is a modification of another measure that requires it to keep the calculations from summing over multiple dates, at least that seems to be how it's working. Maybe it's doing nothing and I'm going crazy, this is also possible.

Re: Receiving Cannot mix text with values error

LYorkToenniges — Fri, 26 Jul 2024 12:12:19 GMT

Sorry, yes I can give sample data, I knew I was forgetting something important.

The two columns used are strictly numerical. They would look something like this:

AC[Production]	DPP[Daily Amount]
12.5	13
32	21
1.125	2

The hoped for outcome would be something like this:

ID	% Commitment
12345	55.4%
54321	Excluded
45321	120.5%
35432	Needs Review
25314	Excluded
24351	68.9%

I got it working without the formatting, it's formatting the output when it's a number thats the problem.

Currently, If I strip out the FORMAT stuff I get:

% Commitment

0.55

Excluded

1.20

Needs Review

I hope this helps, I just looking for a way to take what I have and format the numbers so any solution that obtains that is gold. It doesn't have to be the approach I took.

Re: Receiving Cannot mix text with values error

LYorkToenniges — Fri, 26 Jul 2024 14:45:33 GMT

my god, I solved it. Even though there are text strings in the column I can set the formatting to percent without an error. The solution was so simple my brain hurts from it. I assumed the text strings would cause an error. No DAX modifications were needed to format the numbers. (face palm).

Re: Receiving Cannot mix text with values error

Anonymous — Mon, 29 Jul 2024 05:47:34 GMT

Hi, LYorkToenniges

I'm glad you solved the problem you were experiencing.

Can you please share your method and accept it as a solution? This will help other members of the community with problems similar to yours to be able to find a solution faster. Thank you very much!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Receiving Cannot mix text with values error

LYorkToenniges — Mon, 29 Jul 2024 12:04:18 GMT

It turns out you can sijmply use the formatting in the table view and with will apply that correct formatting to numerical outputs while leaving the texct outputs alone. I had assumed this would cause an error but it has not yet done so.