Shorten DAX Formula

[Anonymous]

Hi Expert

I have the following DAX which i write VAR for each M period - how couldi possibly shorten the measure to something managable

VAR M1 =
Calculate([Running Total],
Input[Year] = MonthYear &&

Input[Terms] = "BTMT" &&

Input[BB/FF] = "BB" &&

Input[Time Period No] ] = " &&

Input[BB] = Product &&
Input[M Column] = "M1"

VAR M2 =
Calculate([Running Total],
Input[Year] = MonthYear &&

Input[Terms] = "BTMT" &&

Input[BB/FF] = "BB" &&

Input[Time Period No] ] = " &&

Input[BB] = Product &&
Input[M Column] = "M2"

Return 

M1+M2

the issue is i am doing this for all period upto M45 hence the DAX has become nearly 400 lines long.

[amitchandak]

@Anonymous , With your formula, Not sure where calculate is ending

Try this to merge

Input[M Column] in {"M1", "M2", "M3" ,//Add others

}

[Anonymous]

Hi Amit - this is not working as i the value for each M value has to end up in the correct Month Column...in my matrix table...

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please try to use the following measure:

M1+M2 = Calculate([Running Total],
Input[Year] = MonthYear &&

Input[Terms] = "BTMT" &&

Input[BB/FF] = "BB" &&

Input[Time Period No] ] = " &&

Input[BB] = Product &&
Input[M Column] IN{"M1","M2"})

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon....

Can you kindly have a look at this question please.

https://community.powerbi.com/t5/Desktop/Add-values-Via-a-Measure-to-a-Specific-Date/m-p/1765401

[amitchandak]

@Anonymous ,What are you trying to achieve here. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.