Filtered Measures

[Anonymous]

Hi,

Is there a way to create a measure, but have conditions?

For example, I have created 10+ tables because I need my formula to filter out certain things based on Month, Year, or Internal/External Crews.

An example of a formula for External Crews in 2019 of December is:

Avg ext Productive Days chillum Dec 2019 = sum('Distinct Dates ext Dec 2019'[externalchillum])/(distinctcount('Distinct Dates ext Dec 2019'[external chillum])-1)

I created a similar formla for External Crews in 2019 of November:

Avg ext Productive Days chillum nov 2019 = sum('Distinct Dates ext Nov 2019'[externalchillum])/(distinctcount('Distinct Dates ext Nov 2019'[external chillum])-1)

To get my tables, I grabbed data from the parent table, Crew Productivity. 

To get my Distinct Dates ext Nov 2019 table, I use this formula.

Distinct Dates ext Nov 2019 = calculatetable('Distinct Dates ext',FILTER('Distinct Dates ext',left('Distinct Dates ext'[Official Date],2)="11"),filter('Distinct Dates ext',right('Distinct Dates ext'[Official Date],4)="2019"))

Overall, I was wondering if there was a way to create my formulas in the Crew Productivity table, if possible, so I would not keep having to filter out from the Crew Productivity table, and continue to create more and more tables as time goes on...
Please let me know if this question is confusing...

Thank you!
Sarah

[amitchandak]

I think I did not get it completely. But to do any operation with time, create a calendar table. And join it with the date of you fact. Tell us what formula you want to drive(Functional not the power Bi formula)

In between refer

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

maybe to make it simpler, @amitchandak ,

i want to create a measure that says something like

"If month=December then use this measure, else if month=November, then use this measure, else if...."
For multiple conditions...

thank you!
Sarah

[Anonymous]

Hi @amitchandak,

I sort of did create a calendar table for each situation... for example, for External Crews for the month of December in 2019, this is the table I created:

Distinct Dates ext Dec 2019 = calculatetable('Distinct Dates ext',FILTER('Distinct Dates ext',left('Distinct Dates ext'[Official Date],2)="12"),filter('Distinct Dates ext',right('Distinct Dates ext'[Official Date],4)="2019"))

Overall, I want to find the average days crews were productive. I have three filters. Internal/External, Month, and Year.

As of now, my average productive days formula works because when creating a seperate table, it filteres the Month/Year/Internal or External for me...

I want to create multiple measures, if possible, that takes into account Month, Year, and Internal/External 

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...