Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alexa_0028
Resolver II
Resolver II

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

3 ACCEPTED SOLUTIONS
smpa01
Super User
Super User

@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]
    )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

@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 )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

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

View solution in original post

6 REPLIES 6
alexa_0028
Resolver II
Resolver II

@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.

@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 )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
smpa01
Super User
Super User

@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]
    )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.