Forum Discussion

alexa_0028's avatar
alexa_0028
Resolver 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",'Product'[Product],

"L6",

if('Product'[P. Code] IN VALUES('Mapping'[P. Code]),'Mapping'[L2],"not mapped")

))

The table is very slow and not giving results because of highlighted column (as there are multiple values of Mapping'[L2]  found in a cross join for a single P.Code. If I replace it with a constant string it works. How can I fix it any idea please?

I am working in a composite model, therefore I cannot use other joins.Cross join is my only option here.
Please help me

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 = [...]
)

6 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    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]
    )
)
    • alexa_0028's avatar
      alexa_0028
      Resolver II
      4 years ago

      Hi smpa01 
      All the solutions worked well, thank you so much for your help.
      This was very useful indeed. Huge Kudos to you.



  • alexa_0028's avatar
    alexa_0028
    Resolver 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
      Community 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
        Super 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 = [...]
)