topic Re: How to make IF statements and return a result when a column from other table has equal lines in DAX Commands and Tips

How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Thu, 01 Jun 2023 09:42:36 GMT

Hey, I am having some troubles here. Can you help me, please?

I made the following code to calculate a new column:

------

CenterOfCost =

IF(ISBLANK(F[NDoc_Date_Org]);BLANK();

IF(

(Map[CC_Distinct]) = 1;

LOOKUPVALUE(Map[Center cost]; Map[NDoc_DateL_Org]; F[NDoc_Date_Org])

;

IF(Map[CC_Distinct] <> 1; "Multiple Org";

BLANK())

)

-------

I have created some example tables for better understanding:

I am not getting the right result for my CenterOfCost and i would like to get the result on my example.

Can you help?

Thanks,

Sofia

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Thu, 01 Jun 2023 10:17:51 GMT

Hi asofiacgraca

do you a relationship between the two tables?

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Thu, 01 Jun 2023 10:47:43 GMT

asofiacgraca
If there is a relationship between both tables then

CenterOfCost = SELECTEDVALUE ( Map[Center cost]; "Multiple Org" )

Otherwise use

CenterOfCost = VAR T = FILTER ( Map; Map[NDoc_DateL_Org] = F[NDoc_Date_Org] ) RETURN IF ( NOT ISEMPTY ( T ); IF ( COUNTROWS ( T ) = 1; MAXX ( T; Map[Center cost] ); "Multiple Org" ) )

Re: How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Thu, 01 Jun 2023 11:14:29 GMT

Hey, i don´t have relationships between tables.

It seems not to be working. I only have "Multiple Org" showing and you don't make any reference to CC_Distinct?

Can you help?

Thanks,

Sofia

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Thu, 01 Jun 2023 11:47:34 GMT

asofiacgraca
That won't be necessary as CC_Distinct simply says how many rows the same NDoc_DateL_Org presents in. Unless the sample data is oversimplified.

Re: How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Thu, 01 Jun 2023 11:54:10 GMT

But it is not working 😕

Do you have any idea?

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Thu, 01 Jun 2023 12:19:26 GMT

asofiacgraca
Yes, my idea is that you provide more realistic sample of data since based on the sample provided it works.

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Thu, 01 Jun 2023 12:22:33 GMT

asofiacgraca
Here is another version that might work with the real data.

CenterOfCost = VAR T = FILTER ( Map, Map[NDoc_Date_Org] = F[NDoc_Date_Org] ) RETURN IF ( NOT ISEMPTY ( T ), IF ( MAXX ( T, Map[CC_Distinct] ) = 1, MAXX ( T, Map[Center cost] ) & "", "Multiple Org" ) )

Re: How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Thu, 01 Jun 2023 14:36:53 GMT

Hey

Sorry i can't provide more information since its confidential and also the volume of data a lot.

The is just returning empty spaces or 1 Center Of Cost, but that's it 😕

Also is it possible to ignore blank spaces on Map?

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Thu, 01 Jun 2023 14:48:42 GMT

asofiacgraca

Can you please explain further? I could not fully understand what results did you obtain. Which dax have you tried?

Re: How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Thu, 01 Jun 2023 15:22:01 GMT

I tried the last code you gave me!

For example (see picture), i can have spaces that are blank and my CC_Distinct will say there are 2 values, i want it to ignore if one is a blank space, and return only the value (in the picture it would be the number 1). - last rows of the tables.

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Thu, 01 Jun 2023 16:56:06 GMT

asofiacgraca

Assuming Map[Center cost] is text data type, please try.

CenterOfCost =
VAR T =
FILTER (
Map,
Map[NDoc_Date_Org] = F[NDoc_Date_Org]
&& Map[NDoc_Date_Org] <> ""
)
RETURN
IF (
NOT ISEMPTY ( T ),
IF (
MAXX ( T, Map[CC_Distinct] ) = 1,
MAXX ( T, Map[Center cost] ),
"Multiple Org"
)
)

otherwise please try
CenterOfCost =
VAR T =
FILTER (
Map,
Map[NDoc_Date_Org] = F[NDoc_Date_Org]
&& Map[NDoc_Date_Org] <> BLANK ( )
)
RETURN
IF (
NOT ISEMPTY ( T ),
IF (
MAXX ( T, Map[CC_Distinct] ) = 1,
MAXX ( T, Map[Center cost] ) & "",
"Multiple Org"
)
)

Re: How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Fri, 02 Jun 2023 15:00:45 GMT

Hello,

I can't make it to work properly, it only shows one number and the rest is empty or blank:

But thank you for the effort.

Best regards,

Sofia

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Fri, 02 Jun 2023 16:24:59 GMT

asofiacgraca

It could be a space or a special character. Try to create a measure

LEN ( MAX ( Map[NDoc_Date_Org] ) )

and see what you get

Re: How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Mon, 05 Jun 2023 09:09:45 GMT

Hey tamerj1

I got a value of 25.

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Mon, 05 Jun 2023 10:15:50 GMT

asofiacgraca

I mean what value you see nex to the blank. You need to place the measure in a table visual along with the column

Re: How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Mon, 05 Jun 2023 10:22:10 GMT

That's what i get

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Mon, 05 Jun 2023 10:39:17 GMT

asofiacgraca
I don't see what you see but I thought we were talking about Map[NDoc_Date_Org] column. This CentroCusto_F column was not in the picture before and I have no idea to which table it belongs. Now I'm a bit confused which column contains the blank row? Perhaps it would be a good idea if you re-illustrate everything allover again.

Re: How to make IF statements and return a result when a column from other table has equal lines

asofiacgraca — Mon, 05 Jun 2023 10:43:21 GMT

Hey tamerj1

It is the same, it's just my original file. This CentroCusto_F is the CenterOfCost I told you about and "Medida" is the measure you told me to create now.

Re: How to make IF statements and return a result when a column from other table has equal lines

tamerj1 — Mon, 05 Jun 2023 10:58:25 GMT

asofiacgraca
Ok, just create a calculated column in the same table

LEN ( Map[NDoc_Date_Org] )