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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndresSalomon
Helper II
Helper II

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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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,

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!! 🙂 I'll take a look at the link.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

Is there a relationship between the two tables?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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