cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Return Value based on two text conditions

Hi,

This maybe a very simple solutions, but currently I have a table like below with some sample data;

 ID Budgeted Cost Type Period Value Date 0101 null Capex FY20 PE11 - Feb 20 402.11 01/02/2020 0101 null Opex FY20 PE11 - Feb 20 549.99 01/02/2020 0101 124999,99 Capex Total 0 01/02/2020

Now what I need to do is a calculation over the entire table, to return Budgeted Cost, where the conditions match, for example, [Type] = Capex and [Period] = Total. However I am currently trying to calculate all the Values and add a filter on the calaculate like

``Capex Total TD = CALCULATE(Mesaure), Table[Type] = "Capex", [Period] = "Total"``

If i use the above it returns no result

I then added an all filter as there is a slicer that looks at a particular month;

``Capex Total TD = CALCULATE(Mesaure), Table[Type] = "Capex", [Period] = "Total", ALL(Table)``

When I use this, it returns all the values as the same (there are over a thousand id.

Any help is greatly appreciated

thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Guys

One thing I ommitted previously was that there was a slicer that singled some results to a particular period.

This calculation was taking the whole period, and I added the ALL() funtion to the end looking up the date table, By adding the ALL() funtion straight after the SUM() meant I got the solution I was looking for.

Thank you so much for all your efforts in helping, all of the information provided has been useful!

All the best, and happy data naughting!

5 REPLIES 5
Anonymous
Not applicable

Hi Guys

One thing I ommitted previously was that there was a slicer that singled some results to a particular period.

This calculation was taking the whole period, and I added the ALL() funtion to the end looking up the date table, By adding the ALL() funtion straight after the SUM() meant I got the solution I was looking for.

Thank you so much for all your efforts in helping, all of the information provided has been useful!

All the best, and happy data naughting!

Super User

I hope you have to give like

``Capex Total TD = CALCULATE(sum(table[Budgeted Cost], Table[Type] = "Capex", [Period] = "Total"))``

Null is blank not "NULL" the text and data type is numeric

Anonymous
Not applicable

Thanks so much for replying, I did in the time since posting try that, and I just get no result (literally blank). This issue has been bugging for a few days I have no idea what else to try. I mean I have created a column based on those conditions to return the result, is it possible to return the value of the column?

Community Support

Hi @Anonymous ,

Have you tried the if statement?

``Capex Total TD = IF(MAX(Table[Type]) = "Capex"&&MAX(Table[Period]) = "Total",[measure])``

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

Anonymous
Not applicable

Hi Liang,

I'm really stuck at an impass here!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors