Forum Discussion

elakkiyaselvan's avatar
elakkiyaselvan
Frequent Visitor
1 year ago
Solved

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   ID Name 1 ...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

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

    )

    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

  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

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

     

     

    Hope this helps.

Ritaf1983's avatar
Ritaf1983
Super User
1 year ago

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

)

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

elakkiyaselvan's avatar
elakkiyaselvan
Frequent Visitor
1 year ago

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

 

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

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

     

     

    Hope this helps.