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
yukon
Helper I
Helper I

How to create a measure with value from another table

Hello Gen,

 

I'm trying to do simple pivot table with DAX. That's very simple for you all but it's very hard for me to make it. It taking almost 1 week but still can't figure out.  I would like to show some data from different table on pivot table column. So i use lookup column on my primary table but total result is wrong. I think it should be use measure instead of lookup. But i have no idea to how to get the value from another table using with measure

 

Existing Lookup

 

 

LOOKUPVALUE(PlanSales[Plan Sales Amount],PlanSales[Year],YEAR(Evaluation[Ord. Date]),[Country Code],Evaluation[Sell-to Country Code])

 

 

 

I need to retrive value from "PlanSales" Table base on "Calendar[Year]" and "Sell_to_Country[Sell-to Alpha-2 Code] filter. I try to use below DAX but it giving me Max value even my Calendar Year is 2019.

 

 

 

mPlan Sales Amount:=CALCULATE (
        MAX(PlanSales[Plan Sales Amount]),ALLEXCEPT('Calendar','Calendar'[Year]),ALLEXCEPT(Sell_to_Country,Sell_to_Country[Sell-to Country]))

 

 

 

Plan Sales Table

YearCountry CodePlan Sales Amt.
2019AU100
2020AU150
2019SG150
2020SG120

 

Sample Pivot

Demo.png

 

You can find the sample excel file at here https://www.mediafire.com/file/tl7lfhzjepyemtj/Sampl2.xlsx/file 

 

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @yukon ,

 

We can use the following measures to meet your requirement.

Measure 1:=SUMX(VALUES(Sell_to_Country),CALCULATE(MAX(Evaluation[PSalesAmt])))

 

 

 

Measure 2 = IF (
    ISFILTERED ( 'Evaluation'[Sell-to Country Code] )
        || ISFILTERED ( 'Evaluation'[Sell-to Customer Name] ),
    CALCULATE ( AVERAGE ( Evaluation[PSalesAmt] ) ),
    MAXX (
        GROUPBY (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Year],
            'Calendar'[Week of Year]
        ),
        CALCULATE ( AVERAGE ( Evaluation[PSalesAvgAmt] ) )
    )
)
 

32.png

 

Then We can use the two measures to replace [PSalesAmt] and [PSalesAvgAmt], the result like this,

 

33.png

 

If you have any other questions, please kindly ask here and we will try to resolve it.

 

BTW, excel file as attached.


Best regards,

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lid-msft
Community Support
Community Support

Hi @yukon ,

 

We can use the following measures to meet your requirement.

Measure 1:=SUMX(VALUES(Sell_to_Country),CALCULATE(MAX(Evaluation[PSalesAmt])))

 

 

 

Measure 2 = IF (
    ISFILTERED ( 'Evaluation'[Sell-to Country Code] )
        || ISFILTERED ( 'Evaluation'[Sell-to Customer Name] ),
    CALCULATE ( AVERAGE ( Evaluation[PSalesAmt] ) ),
    MAXX (
        GROUPBY (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Year],
            'Calendar'[Week of Year]
        ),
        CALCULATE ( AVERAGE ( Evaluation[PSalesAvgAmt] ) )
    )
)
 

32.png

 

Then We can use the two measures to replace [PSalesAmt] and [PSalesAvgAmt], the result like this,

 

33.png

 

If you have any other questions, please kindly ask here and we will try to resolve it.

 

BTW, excel file as attached.


Best regards,

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dear @v-lid-msft ,

 

It's awesome and working beautiful. I was spend almost a week to get correct figure but no way. May i ask some question? Is it possible to get same figure without adding Lookup column into "Evaluation" table. It mean we directly take the value from "PlanSales" table and make it measure. I worry about the performance if we add lookup column into "Eveluation". Actually, my table has 1,048,576 rows.

 

Sorry for my cazy question.

 

Thank you so much for excel attach.

 

Regards,

 

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.