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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.