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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
elakkiyaselvan
Frequent Visitor

Custom Column based on Fact table condition

Hello All,

 

I'm looking for a solution to the following scenario: I want to create a new custom column in a dim table based on a condition applied to a fact table.

 

Dim 
IDName
1Direct
2Direct
3Indirect
4Indirect

 

Fact 
IDIndicator
1N
2N
3Y
4N

 

Excepted Output 

DIM   
IDNameCustom ColumnCondition
1DirectDirectIf Dim(Direct) = Fact(Direct) then Direct 
2DirectDirectIf Dim(Direct) = Fact(Direct) then Direct 
3IndirectQDPIf Dim(InDirect) = Fact(InDirect)  & Fact Indicator = Y
then QDP
4IndirectIndirectIf Dim(InDirect) = Fact(InDirect)  & Fact Indicator = N
then InDirect

 

Many thanks in advance.

 

 

 

 

 

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS
Ritaf1983
Super User
Super User

Hi @elakkiyaselvan 

Since you're pulling a value from the Fact table (many-side) into the Dim table (one-side), you must apply an aggregation, like MAXX, MINX, or SELECTEDVALUE. Without it, the engine won’t know which value to return when multiple rows exist.

So yes — your logic is fully achievable as long as the Indicator column in the Fact table has at most one value per ID.

Here’s a DAX expression that does exactly what you need:

 
 
Custom =

var from_Fact = MAXX(RELATEDTABLE('Fact'),'Fact'[Indicator])

RETURN

IF('Dim'[Name]="Direct","Direct",

IF('Dim'[Name]="Indirect" && from_Fact="Y","QDP",

"Indirect")

)
Ritaf1983_0-1751508086744.png

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

Hi @elakkiyaselvan - This cannot be done with a calculated column dim table which only produces 1 row per key.Please check the below logic using calculated table.

 

DimFact Expanded =
FILTER (
    ADDCOLUMNS (
        SELECTCOLUMNS (
            CROSSJOIN ( 'Dim', 'Fact'),
            "DimID", Dim[ID],
            "DimName", Dim[Name],
            "FactID", Fact[ID],
            "Indicator", Fact[Indicator]
        ),
        "Custom Column",
            SWITCH (
                TRUE(),
                [DimName] = "Direct" && [DimID] = [FactID], "Direct",
                [DimName] = "Indirect" && [DimID] = [FactID] && [Indicator] = "Y", "QDP",
                [DimName] = "Indirect" && [DimID] = [FactID] && [Indicator] = "N", "Indirect",
                BLANK()
            )
    ),
    NOT ISBLANK([Custom Column])
)

 

rajendraongole1_0-1751627646095.png

 

Hope this helps.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

4 REPLIES 4
Ritaf1983
Super User
Super User

Hi @elakkiyaselvan 

Since you're pulling a value from the Fact table (many-side) into the Dim table (one-side), you must apply an aggregation, like MAXX, MINX, or SELECTEDVALUE. Without it, the engine won’t know which value to return when multiple rows exist.

So yes — your logic is fully achievable as long as the Indicator column in the Fact table has at most one value per ID.

Here’s a DAX expression that does exactly what you need:

 
 
Custom =

var from_Fact = MAXX(RELATEDTABLE('Fact'),'Fact'[Indicator])

RETURN

IF('Dim'[Name]="Direct","Direct",

IF('Dim'[Name]="Indirect" && from_Fact="Y","QDP",

"Indirect")

)
Ritaf1983_0-1751508086744.png

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983  Thank you for your assistance. Need your assistance on below output as well. I have added additional condition for 3.Incase if have both Indicator for 3 (If 3 = Y QDP, 3 = N Indirect)

 

Excepted Output 

DIM   
IDNameCustom ColumnCondition
1DirectDirectIf Dim(Direct) = Fact(Direct) then Direct 
2DirectDirectIf Dim(Direct) = Fact(Direct) then Direct 
3IndirectQDPIf Dim(InDirect) = Fact(InDirect)  & Fact Indicator = Y
then QDP
3IndirectIndirectIf Dim(InDirect) = Fact(InDirect)  & Fact Indicator = N
then InDirect
4IndirectIndirectIf Dim(InDirect) = Fact(InDirect)  & Fact Indicator = N
then InDirect

 

Hi @elakkiyaselvan - This cannot be done with a calculated column dim table which only produces 1 row per key.Please check the below logic using calculated table.

 

DimFact Expanded =
FILTER (
    ADDCOLUMNS (
        SELECTCOLUMNS (
            CROSSJOIN ( 'Dim', 'Fact'),
            "DimID", Dim[ID],
            "DimName", Dim[Name],
            "FactID", Fact[ID],
            "Indicator", Fact[Indicator]
        ),
        "Custom Column",
            SWITCH (
                TRUE(),
                [DimName] = "Direct" && [DimID] = [FactID], "Direct",
                [DimName] = "Indirect" && [DimID] = [FactID] && [Indicator] = "Y", "QDP",
                [DimName] = "Indirect" && [DimID] = [FactID] && [Indicator] = "N", "Indirect",
                BLANK()
            )
    ),
    NOT ISBLANK([Custom Column])
)

 

rajendraongole1_0-1751627646095.png

 

Hope this helps.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





ChoicePeace
New Member

Hi,

You can use the function switch, and it would be more optimiser then IF.

https://learn.microsoft.com/en-us/dax/switch-function-dax 

 

don't hesitate to see the examples

Best regards,

ChoicePeace

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors