topic Re: DAX Fetch base value of 1 column and multiply it to all the rows of another column in DAX Commands and Tips

DAX Fetch base value of 1 column and multiply it to all the rows of another column

Anonymous — Tue, 06 Sep 2022 09:34:12 GMT

Need help in DAX. I have one table that contains data related to multipliers for each year, like the follows:

***********************************

Table Multiplier

********************************

Another table is there which has multiple 'values' for the year column like the follows:

******************************************

Table Entities

*************************************************

There's a 1- * relation between the tables based on the year column.

Question: What I have to achieve is access the base/starting"Value" of every entity (base year being 2011-12) and multiply only that value by all the rows in the 'Multiplier' column.

The solution for entity A would look something like below:

Base value remains 20 for every year, then that value gets multiplied by the multiplier which changes every year.

Now, to get the Base value, I used the following to create a measure:

Base value = CALCULATE(SUM('Entities'[Value]), Multiplier[Year] = "2011-12")

For the base*multiplier column: Base * multiplier = [Base value] * SUM('Multiplier'[Multiplier])

The result of the above is:

So, it is generating correct base value but then not moving forward to multiply each year's multiplier with the base value. I Tried other filter contexts too using KEEPFILTER, ALL within the CALCULATE, but nothing seems to generate the value I want.

Please help. Thanks in advance.

Some people I know who are good and can help are tagged, but others are most welcome to help too.
v-chenyue-msft parry2k Anonymous amitchandak lbendlin Ashish_Mathur

Re: DAX Fetch base value of 1 column and multiply it to all the rows of another column

parry2k — Tue, 06 Sep 2022 16:20:53 GMT

Anonymous the easiest would be to create a calculated table using the following DAX expression:

Table = ADDCOLUMNS ( CROSSJOIN ( SELECTCOLUMNS ( Entities, "Entity", Entities[Entity], "Value", Entities[Value] ), Multiplier ), "Value with Multiplier", [Value] * [Multiplier] )

✨ Follow us on LinkedIn and to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Re: DAX Fetch base value of 1 column and multiply it to all the rows of another column

v-xiaotang — Wed, 07 Sep 2022 07:29:17 GMT

Hi Anonymous

Thanks for reaching out to us.

>> So, it is generating correct base value but then not moving forward to multiply each year's multiplier with the base value. I Tried other filter contexts too using KEEPFILTER, ALL within the CALCULATE, but nothing seems to generate the value I want.

Can you fill this table with the expected values? thanks

Best Regards,

Community Support Team _Tang

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

Re: DAX Fetch base value of 1 column and multiply it to all the rows of another column

Anonymous — Thu, 08 Sep 2022 03:59:33 GMT

Hi v-xiaotang Thanks for such a prompt reply. The required table would look like this:

Where in, obviously, the values in the last column would be the result of the multiplication shown.

Regards,

Roopansh

Re: DAX Fetch base value of 1 column and multiply it to all the rows of another column

Anonymous — Thu, 08 Sep 2022 05:05:12 GMT

Hi parry2k , Thanks for your reply. Here's your code with v small changes that worked.

New Index table = ADDCOLUMNS ( CROSSJOIN ( SELECTCOLUMNS ( Entities, "Entity", 'Entities'[Entity], " Base year value", CALCULATE(SUM('Entities'[Value]), 'Entities'[Year] = "2011-12" ) ), Multiplier ), "Value with Multiplier", [Base year value] * Multiplier[Multiplier] )

Thanks a bunch man. I'd love to hear from you what should be the best source to learn dax from. I'm currently reading a book by Matt Allington called Supercharge Power bi.