Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
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;

IDBudgeted CostTypePeriodValueDate
0101nullCapexFY20 PE11 - Feb 20402.1101/02/2020
0101nullOpexFY20 PE11 - Feb 20549.9901/02/2020
0101124999,99CapexTotal001/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!

View solution in original post

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!

amitchandak
Super User
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?

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, 

Thanks for you're answer, Howvever I am still getting no results from this solution. 

 

I'm really stuck at an impass here!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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