Forum Discussion

alexa_0028's avatar
alexa_0028
Icon for Resolver II rankResolver II
4 years ago
Solved

Create table with cross join filtering

Hi All,  I have a DASX like below for calculating table : Calculated Table = DISTINCT(SELECTCOLUMNS( CROSSJOIN('Product','Mapping'), "Product Code",'Product'[P. Code], "Product Description",...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    alexa_0028  does this improve

    Calculated Table =
DISTINCT (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            'Product',
            "L6",
                VAR _lookup = 'Product'[P. Code]
                VAR _looupValue =
                    CALCULATE ( MAX ( 'Mapping'[L2] ), TREATAS ( { _lookup }, 'Mapping'[P. Code] ) )
                RETURN
                    IF ( _looupValue = BLANK (), "not mapped", _looupValue )
        ),
        "Product Code", [P. Code],
        "Product Description", [Product],
        "L6", [L6]
    )
)
  • smpa01's avatar
    smpa01
    4 years ago

    alexa_0028  is this any better

    Calculated Table =
ADDCOLUMNS (
    SUMMARIZE ( 'Product', "Product Code", [P. Code], "Product Code", [P. Code] ),
    "L6",
        VAR _lookup = 'Product'[P. Code]
        VAR _looupValue =
            CALCULATE ( MAX ( 'Mapping'[L2] ), TREATAS ( { _lookup }, 'Mapping'[P. Code] ) )
        RETURN
            IF ( _looupValue = BLANK (), "not mapped", _looupValue )
)
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Your SUMMARIZE looks strange to me. Did you mean to write this?

    Calculated Table =
SELECTCOLUMNS (
    SUMMARIZE ( 'Product', 'Product'[P. Code], 'Product'[Product] ),
    "Product Code", 'Product'[P. Code],
    "Product Description", 'Product'[Product],
    "L6",
        VAR _lookup = [...]
)
alexa_0028's avatar
alexa_0028
Icon for Resolver II rankResolver II
4 years ago

smpa01 
Thank you so much for the help, it has solved my problem and now I am able to get the values.
However, it is loading values very slowly in the visual table filter. 

Could you please suggest some improvements?

Looking forward to hearing from you.

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    alexa_0028  is this any better

    Calculated Table =
ADDCOLUMNS (
    SUMMARIZE ( 'Product', "Product Code", [P. Code], "Product Code", [P. Code] ),
    "L6",
        VAR _lookup = 'Product'[P. Code]
        VAR _looupValue =
            CALCULATE ( MAX ( 'Mapping'[L2] ), TREATAS ( { _lookup }, 'Mapping'[P. Code] ) )
        RETURN
            IF ( _looupValue = BLANK (), "not mapped", _looupValue )
)
    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      Your SUMMARIZE looks strange to me. Did you mean to write this?

      Calculated Table =
SELECTCOLUMNS (
    SUMMARIZE ( 'Product', 'Product'[P. Code], 'Product'[Product] ),
    "Product Code", 'Product'[P. Code],
    "Product Description", 'Product'[Product],
    "L6",
        VAR _lookup = [...]
)
      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        AlexisOlson  I meant to write this; made a mistake while typing. Thanks for spotting.

        Table = 
ADDCOLUMNS (
    SUMMARIZE ( 'Product', [P. Code], [Product] ),
    "L6",
        VAR _lookup = 'Product'[P. Code]
        VAR _looupValue =
            CALCULATE ( MAX ( 'Mapping'[L2] ), TREATAS ( { _lookup }, 'Mapping'[P. Code] ) )
        RETURN
            IF ( _looupValue = BLANK (), "not mapped", _looupValue )
)