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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
asofiacgraca
Helper I
Helper I

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

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:

asofiacgraca_0-1685612454937.png

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@asofiacgraca 
Please refer to attached sample file with the proposed solution

1.png

CenterOfCost = 
VAR CurrentNdocDateOrg = 'Table F'[Ndoc_Date_Org]
VAR T =
    FILTER (
        CALCULATETABLE ( 
            VALUES ( 'Table Map'[Center Cost] ),
            'Table Map'[NDoc_Date_Org] = CurrentNdocDateOrg
        ),
        'Table Map'[Center Cost] <> BLANK ( )
    )
RETURN
    IF (
        NOT ISEMPTY ( T ),
        IF ( COUNTROWS ( T ) = 1, MAXX ( T, 'Table Map'[Center Cost] ) & "", "Multiple Org" )
    )

View solution in original post

26 REPLIES 26
tamerj1
Super User
Super User

@asofiacgraca 
Please refer to attached sample file with the proposed solution

1.png

CenterOfCost = 
VAR CurrentNdocDateOrg = 'Table F'[Ndoc_Date_Org]
VAR T =
    FILTER (
        CALCULATETABLE ( 
            VALUES ( 'Table Map'[Center Cost] ),
            'Table Map'[NDoc_Date_Org] = CurrentNdocDateOrg
        ),
        'Table Map'[Center Cost] <> BLANK ( )
    )
RETURN
    IF (
        NOT ISEMPTY ( T ),
        IF ( COUNTROWS ( T ) = 1, MAXX ( T, 'Table Map'[Center Cost] ) & "", "Multiple Org" )
    )

Hey @tamerj1

That's perfect! I really appreciate it 🙂

 

Best regards,

Sofia 

tamerj1
Super User
Super User

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

1.png

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" )
    )

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?

@asofiacgraca 

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

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.

asofiacgraca_0-1685632888288.png

 

@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"
)
)

Hello,

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

asofiacgraca_0-1685717991310.png

But thank you for the effort.

 

Best regards, 

Sofia

@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

Hey @tamerj1 

I got a value of 25. 

@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

That's what i get

asofiacgraca_0-1685960522206.png

 

@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.

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.

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

LEN ( Map[NDoc_Date_Org] ) 

I get this error due to the fact of rows with the same content, i guess: "It was not possible to determine a unique value for the "NDoc_DataLanca_Emp" column in the "MapDeviations" table."

@asofiacgraca 
So the NDoc_DataLanca_Emp column is the one which have the "balnk" row? Then create the column in same table.

No, it doesn't have blank values. It has the same values on multiple rows.

@asofiacgraca 

I believe it's the time when you need to place some screenshots with proper explanation. You may hide any sensitive data and use aliases if needed. 

So, i created a powerbi with the example i gave you before, for better understanding.

This is the original content and what i want to get is the CenterOfCost highlighted.

asofiacgraca_1-1685978966445.png

 

 As you can see below, the last row returns "Multiple Org" while it should return the number 1.

asofiacgraca_0-1685978897115.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors