cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndresSalomon Regular Visitor
Regular Visitor

Measure based on a condition from other table

Hi Community, hoping you can help me with this task.

 

I have this data table tblForecast:

 

Category__________Month_____________Cost
Cat1________________Sep_______________100

Cat1________________Dec_______________104

Cat6________________Oct_______________102

Cat6________________May_______________10

Cat2________________Jan_______________120

Cat3________________Apr_______________900

.... and so on. Lots of combinations

 

And a look-up table of the months (connected by 1 to many to the tblForecast):

 

Month_______ForecastOrActuals
Sep__________________A
Oct__________________A
Nov__________________A
Dec__________________A
Jan__________________F
Feb__________________F
Mar__________________F
Apr__________________F
May_________________F
Jun__________________F
Jul__________________F
Aug__________________F

So, I need to create a measure that do the following:

 

"Sum the Cost column in tblForecast but only for those months that have an "F" in the column "ForecastOrActuals" from the table month"

 

Please let me know if you need further details. As always, thank you very much in advance!

 

Kind regards,

Andy.-

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Measure based on a condition from other table

I believe what you want is something along the lines of:

 

Measure = CALCULATE(SUM(TblForecast[Cost]),FILTER(RELATEDTABLE(Months),[ForecastOrActuals]="F"))

Let me know if that works. If not, I'll try to recreate your data and test.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


5 REPLIES 5
Super User
Super User

Re: Measure based on a condition from other table

Is there a relationship between the two tables?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


AndresSalomon Regular Visitor
Regular Visitor

Re: Measure based on a condition from other table

Hi @Greg_Deckler, yes there is. A one to many relationship.
Super User
Super User

Re: Measure based on a condition from other table

I believe what you want is something along the lines of:

 

Measure = CALCULATE(SUM(TblForecast[Cost]),FILTER(RELATEDTABLE(Months),[ForecastOrActuals]="F"))

Let me know if that works. If not, I'll try to recreate your data and test.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


AndresSalomon Regular Visitor
Regular Visitor

Re: Measure based on a condition from other table

Absolutely amazing @Greg_Deckler! Worked perfectly. And it will be updated automatically because the column ForecastOrActuals has a DAX formula that put A or F based on other tables conditions. I created a post that is related to this one, maybe you can take a look at it:

 

https://community.powerbi.com/t5/Desktop/Execute-a-mesure-based-on-a-condition/m-p/345453

I need to start using RELATED and RELATEDTABLE... Still newbie into this world. 


Thank you very much! Robot Happy

Regards,

Super User
Super User

Re: Measure based on a condition from other table

Hah! My DAX must be getting better! Usually when I try to write a formula from memory instead of recreating the scenario I make some stupid syntax error!! Smiley Happy I'll take a look at the link.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!