topic Re: create measure depend on other table muliple columns in DAX Commands and Tips

create measure depend on other table muliple columns

Anonymous — Tue, 10 Aug 2021 05:58:42 GMT

Hi everyone,
I m new to DAX and struggling for the last 24 hours to create the measure based on another table but couldn't get it and found the message ( a table of multiple values was supplied where a single value was expected. My calculated column is working fine but I want to create a measure.

calculated column=CALCULATE (
    LASTNONBLANK ( Tariff_ExpTHC1[Tariff], 1 ),
    FILTER (
        Tariff_ExpTHC1,
        'Document Line'[Size2] = Tariff_ExpTHC1[Size]
            && 'Document Line'[Activity No_] = Tariff_ExpTHC1[No.]
            && 'Document Line'[Gross Weight] >= Tariff_ExpTHC1[From_Wt]
            && 'Document Line'[Gross Weight] <= Tariff_ExpTHC1[To_Wt]
            && 'Document Line'[LEO Date] >= Tariff_ExpTHC1[From_Date]
            && 'Document Line'[LEO Date] <= Tariff_ExpTHC1[To_Date]
    )
)

Tariff_Ex_Document_Line (measure) = CALCULATE(

MAXX(Tariff_ExpTHC1,Tariff_ExpTHC1[Tariff]),

TREATAS(

SUMMARIZE('Document Line’, ‘Document Line'[Activity No_],

'Document Line'[Size2]),

Tariff_ExpTHC1[No.],

Tariff_ExpTHC1[Size]

FILTER(

Tariff_ExpTHC1,

VALUES('Document Line'[Gross Weight])>=Tariff_ExpTHC1[From_Wt]

&& VALUES('Document Line'[Gross Weight])<=Tariff_ExpTHC1[To_Wt]

&& VALUES('Document Line'[LEO Date])>=Tariff_ExpTHC1[From_Date]

&& VALUES('Document Line'[LEO Date])<=Tariff_ExpTHC1[To_Date]

)

thanks

Re: create measure depend on other table muliple columns

Anonymous — Tue, 10 Aug 2021 09:59:45 GMT

Using CALCULATE in calculated columns is one VERY BAD idea because you are invoking context transition on each and every row. This has many consequences. One of them being slow code.

Re: create measure depend on other table muliple columns

Anonymous — Tue, 10 Aug 2021 16:05:22 GMT

that is why I need help to create a measure

Re: create measure depend on other table muliple columns

Anonymous — Wed, 11 Aug 2021 09:46:48 GMT

Hi Anonymous

First, you have to describe clearly what you want to achieve. Second, measures are not calculated columns. They work in a totally different way and serve a different purpose. There's no direct translation of a calculated column into a measure. So, to get a good answer you have to do the above and I would also whole-heartedly recommend reading this: How to Get Your Question Answered Quickly - Microsoft Power BI Community

Then, after following all the advice, you may stand a good chance to get a good answer.