Forum Discussion

MikeHendriks's avatar
MikeHendriks
Icon for Helper I rankHelper I
2 years ago
Solved

Combine 2 dimension fields to 1 display column

I have a datamodel with 2 fact tables and some dimensions. All dimensions are connected to both facts as you can see here:     Example PBIX can be found on https://filebin.net/w6w7zba68rdte1...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MikeHendriks ,

    Thanks for reaching out to us with your problem. According to your description, it seems that you want to get the value from another table base on the specific conditions. I tried to open your shared link, but I got the following error. Could you please share it again and give the proper permission? Please exclude the sensitive info before share the file. Thank you.

    In addition, you can create the calculated column as below to get them:

    1. Create the calculated columns in the table 'Fact 1'

    Column1 in F1 =
IF (
    IFERROR ( SEARCH ( "Co", 'Fact 1'[Type], 1, 0 ), 0 ) > 0,
    MAX ( 'Co'[Column] )
)

Column2 in F1 =
IF (
    IFERROR ( SEARCH ( "Pa", 'Fact 1'[Type], 1, 0 ), 0 ) > 0,
    MAX ( 'Pa'[Column] )
)

    2.  Create the calculated columns in the table 'Fact 2'

    Column1 in F2 =
IF (
    'Fact 2'[Dim3ID] = -1,
    MAX ( 'Pa'[Column] )
)

Column2 in F2 =
IF (
     'Fact 2'[Dim3ID] <> -1
     MAX ( 'Co'[Column] )
)

    Best Regards

MikeHendriks's avatar
MikeHendriks
Icon for Helper I rankHelper I
2 years ago

Thanks amitchandak, but I have a fixed cube model which I am connecting to so no Power Query options and also no combining dimensions... So that the reason that I opted for a DAX option. Is there an option in that direction as well?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi MikeHendriks ,

    Thanks for reaching out to us with your problem. According to your description, it seems that you want to get the value from another table base on the specific conditions. I tried to open your shared link, but I got the following error. Could you please share it again and give the proper permission? Please exclude the sensitive info before share the file. Thank you.

    In addition, you can create the calculated column as below to get them:

    1. Create the calculated columns in the table 'Fact 1'

    Column1 in F1 =
IF (
    IFERROR ( SEARCH ( "Co", 'Fact 1'[Type], 1, 0 ), 0 ) > 0,
    MAX ( 'Co'[Column] )
)

Column2 in F1 =
IF (
    IFERROR ( SEARCH ( "Pa", 'Fact 1'[Type], 1, 0 ), 0 ) > 0,
    MAX ( 'Pa'[Column] )
)

    2.  Create the calculated columns in the table 'Fact 2'

    Column1 in F2 =
IF (
    'Fact 2'[Dim3ID] = -1,
    MAX ( 'Pa'[Column] )
)

Column2 in F2 =
IF (
     'Fact 2'[Dim3ID] <> -1
     MAX ( 'Co'[Column] )
)

    Best Regards