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