topic Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text in DAX Commands and Tips

Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

t-irp — Wed, 08 Feb 2023 21:49:20 GMT

Hello, I did a search and found a similar post but despite my efforts to solve my own issue, I am blocked and could use some guidance. I did follow the steps in that other post, which were quite clear by those who responded.

I am creating a Financial report where I need to group "general ledger" codes that are text values but with ranges such as >1000 and <1999, 2000 to 2050, etc. I have 50+ total. I don't want to use the "group by" feature as that seems to only detect values that exist "today" in the ERP. If the Finance team wants to add some new GL codes like 2049, I am looking for a query that will detect that without them having to remember to then add more to the "group by". The goal here is to detect GL codes in ranges.

The GL_Code column is of type TEXT, coming in that way from the vendor. We cannot change this in the master system, but tried changing in Power BI but did not see a change in my error messages. As of now, the value for GL has been changed to Whole number," My calculated column is of type "whole number."

My query will be massive, but step 1 is getting this working.

ReportValue (DAX) = Switch(TRUE(), NOT(ISBLANK(VALUE(GL_BALANCE_MC[Cost_Center]))) = 1000 , "Cash")

also tried

ReportValue (DAX) = Switch(TRUE(), NOT(ISBLANK(FORMAT(GL_BALANCE_MC[Cost_Center],"Standard"))) == 1000 , "Cash")

and

ReportValue (DAX) = Switch(TRUE(), FORMAT(GL_BALANCE_MC[Cost_Center],"Standard") == 1000 , "Cash")

All give me

DAX comparison operations do not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

I have also tried with a single = and a ==.

Does anyone see an obvious error or misunderstanding on my part. I hope I described the issue.

Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

FreemanZ — Wed, 08 Feb 2023 22:27:43 GMT

hi t-irp

what kind of data do you have for column GL_BALANCE_MC[Cost_Center]?

Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

t-irp — Wed, 08 Feb 2023 22:31:06 GMT

That data coming from the vendor DB is "text". I have selected that column in Power BI and changed to "whole number" but that is not solving my issue, so I am doing something else wrong it seems.

Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

FreemanZ — Wed, 08 Feb 2023 22:37:17 GMT

hi t-irp

regardless its data type, what kind of value do you have?

Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

t-irp — Wed, 08 Feb 2023 22:38:56 GMT

They are numbers such as 1000, 1200, 1351, etc, but some are empty/null

Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

FreemanZ — Wed, 08 Feb 2023 22:46:35 GMT

hi t-irp

not sure about your use case. try like:

ReportValue (DAX) =

Switch( TRUE(),NOT(ISBLANK(VALUE(GL_BALANCE_MC[Cost_Center]))) , "Cash")

or why you want to compare with 1000?

Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

t-irp — Wed, 08 Feb 2023 23:09:18 GMT

Longer term, I need to create a report that shows this, but for 54 rows.

......

Inventory	1300	1399	Some number
Prepaids	1400	1439	Some number
Other Current Assets	1440	1499	Some number
Total Current Assets	1000	1499	Some number

.......

Note that "Total Current Assets" includes a collection of GL_codes that were already chosen ealier for other groupings. In my mind, I think I should have something like;

ReportValue (DAX) = Switch(TRUE(), NOT(ISBLANK(VALUE(GL_BALANCE_MC[Cost_Center]))) >= 1000 && NOT(ISBLANK(VALUE(GL_BALANCE_MC[Cost_Center]))) <= 1499 , "Total Current Access")

But 54 of these in total, one for each line item aggregating the appropriate GL_Codes.

Cash

AR - Trade

AR - Other

Costs in Excess of Billings

Intercompany Assets

50 more....

Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

t-irp — Thu, 09 Feb 2023 14:57:14 GMT

I made a lot more progress. It seems that the value 1000 is a string, not a number.

This

ReportValue (DAX) = Switch(TRUE(), FORMAT(GL_BALANCE_MC[Cost_Center],"Standard") = FORMAT(1000,"Standard"), "Cash")

gets me past the error. Hopefully it helps someone

Re: Another DAXComparison Operations Do Not Support Comparing Values Of Type Integer With Type Text

t-irp — Mon, 27 Feb 2023 13:33:33 GMT

Sorry for the late reply, = the issue was that "1000" needed to be a string for whatever reason.