Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DAX FUNCTION

I have a formula from excel and it really works and produces the output needed. However I need to convert this formula into Dax and use it in PowerBI. I have tried both || and && and its still not wo...
Need Help
Tips and Tricks
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    You use the VALUE function, which converts a text string that represents a number to a number. For example, VALUE("3") returns number 3.  What's the type of ORG column? It is text like "32", then VALUE(ORG) returns number type, which is nou used to compare with text type, like VALUE(ORG)<“699”. Mybe you can change it to VALUE(ORG)<VALUE(“699”), and also mix corresponding arguments in Anonymous posted formula using similar way. Please try and check if it work fine.

    Best Regards,
    Angelia

  • Anonymous's avatar
    Anonymous
    9 years ago

    You need to indent and format your code so you can read it. You have the wrong number of parentheses in the wrong places.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Actually, it has 30 arguments. I've formatted your code so it's readable.

 

=IF(
	OR(
		AND(
			OBJECT="6265",
			VALUE(ORG)<“699”
		),
		OBJECT="6255",
		AND(
			OBJECT="6256",
			VALUE(ORG)<“699”
		),
		OBJECT="6257",
		OBJECT="6258",
		OBJECT="6259",
		OBJECT="8911",
		AND(
			OBJECT="6112",
			VALUE(ORG)<“699”
		),
		AND(
			SUB_OBJECT="XH",
			VALUE(ORG)<>“816”
		),
		SUB_OBJECT="LQ",
		AND(
			SUB_OBJECT="DF",
			VALUE(ORG)<>”961”
		),
		AND(
			SUB_OBJECT="TE",
			VALUE(ORG)<>“816”
		),
		AND(
			SUB_OBJECT="JF",
			VALUE(ORG)<“699”
		),
		AND(
			SUB_OBJECT="JP",
			VALUE(ORG)<“699”
		),
		AND(
			SUB_OBJECT="CM",
			VALUE(ORG)<>“816”
		),
		AND(
			SUB_OBJECT="48",
			VALUE(ORG)<>“969”
		),
		OBJECT="6116",
		OBJECT="6119",
		OBJECT="6129",
		OBJECT="6141",
		OBJECT="6142",
		OBJECT="6143",
		OBJECT="6145",
		OBJECT="6146",
		OBJECT="6149",
		OBJECT="6144",
		OBJECT="6113",
		OBJECT="6126",
		VALUE(ORG)>“983”,
		AND(
			FUNCTION="12",
			VALUE(ORG)<“699”
		)
	),
	"NON",
	"CON"
)

Columns should be referred to in square brackets, by the way. TableName[Object] for instance.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

You need to be specific about what you mean by "not working". Are you getting an error message? What error message?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    The error message was about the number of acceptable arguments- saying it only accepts 2

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Your formula should follow a pattern like

     

    =IF(
	AND(
		TableName[OBJECT] = "6265",
		VALUE(TableName[ORG])<“699”
	) ||
	TableName[OBJECT]="6255" ||
	AND(
		TableName[OBJECT]="6256",
		VALUE(TableName[ORG])<“699”
	) ||

.... etc etc
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Here is the error message: