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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vitexo87
Post Prodigy
Post Prodigy

How to add a custom column with the "IF" function?

I have two different columns of different tables, in table A I have the column Year_Rest and in table B I have the column Year_Empenho_Rest, and I'm creating a report that I need to have a custom column:

= If (Year_Rest> Year_Empenho_Rest, Year_Empenho_Rest)

I'm trying to add a polite column with this condition in one of the two tables, but I'm not getting it, what should I do?

Note: the two tables relate directly.

1 ACCEPTED SOLUTION

I am sorry, the screenshots are not in English, so I may provide a wrong answer now:

I assumed that you have a year field in Table1 and another year field in Table2. Each row in your fact table is connected differently to Table1 and Table2.

 

You can add a calculated column (not measure) in the fact table:

Displayed Year = IF ( RELATED ( Table1[Year] ) > RELATED ( Table2[Year] ) , RELATED ( Table1[Year] ) , RELATED ( Table2[Year] ) )

 

Hope it helps. You may also use FIRSTNONBLANK in a measure, but I couldn't understand the language, so I may be wrong here.

If I am wrong, you can dsecribe below exmaples of  three rows for each of the three tables with column names in English, and how they are connected. 

View solution in original post

6 REPLIES 6

Hi @vitexo87

 

Could you please provide more information how the two tables are related to each other? Can you share more information on the main columns you use in each table for the visualization?

 

In addition, in your IF statement above you didn't mention the ELSE part. Is it a kind of MIN?

 

Thank you,

Gil

Sorry I said that the two tables relate directly but not true, actually between them has the fact table, and follows how the two tables are related in my power bi application and in the example I gave is an expression I used in qlikview, this Expression in qlikview I used directly in the report, there I have this feature

 

 

Untitled 1.pngUntitled.png

Sorry. The logic is still not clear. What would you want to calculate? The earliest of the two dates? Can you explain or mockup the visual you wish to have? What will you show as Values?

@DataChant

I have these two fields of this two tables, these fields are years, and I want to display this information in a simple table report, however I have to apply a condition where, if year A is greater than year B show only the year A.

I am sorry, the screenshots are not in English, so I may provide a wrong answer now:

I assumed that you have a year field in Table1 and another year field in Table2. Each row in your fact table is connected differently to Table1 and Table2.

 

You can add a calculated column (not measure) in the fact table:

Displayed Year = IF ( RELATED ( Table1[Year] ) > RELATED ( Table2[Year] ) , RELATED ( Table1[Year] ) , RELATED ( Table2[Year] ) )

 

Hope it helps. You may also use FIRSTNONBLANK in a measure, but I couldn't understand the language, so I may be wrong here.

If I am wrong, you can dsecribe below exmaples of  three rows for each of the three tables with column names in English, and how they are connected. 

Is almost that but it helped me to solve the problem, I have not yet reached the ideal value but I believe that now it is a matter of data modeling myself, I created a new column with the expression:

Year Commitment = IF (RELATED (DMEGF_Ano_Resto [Year_Restore])> RELATED (DMEGF_Empenho_Resto [Year_Empenho_Restore])) RELATED (DMEGF_Empenho_Resto [Year_Empenho_Resto]))


Thank you for your help

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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