Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
I have about 10 measures that read the data from same table. I apply some inclusion rules on the data before performing any calculation in each measure. The problem is that I have not found an efficient way of applying inclusion rules within the measure. For example, if a measure calculates the difference between two dates (after applying inclusion rules), it looks like as follow:
measure 1 =
Var start_date = Calculate(Max(Table[startDate]),Filter(Table,[inclusion1]&&[inclusion2]&&[inclusion3]&&[inclusion4]&&[inclusion5]))
Var end_date = Calculate(Max(Table[endDate]),Filter(Table,[inclusion1]&&[inclusion2]&&[inclusion3]&&[inclusion4]&&[inclusion5]))
Return
Datediff(start_date,end_date,hour)
The above example is a simple example, but in some measure, I have 6 to 7 variables and writing same Filter() statement with every variable is cumbersome. Is there any other smart way of filtering the data within measure
Solved! Go to Solution.
This is what I was suggesting, although I am not clear about your "inclusion" terms. Is that shorthand? What is the actual text for [inclusion1], [inclusion2], ...?
NewMeasure =
VAR filtertable =
FILTER (
ALL (
Table[inclusion_col1],
Table[inclusion_col2],
Table[inclusion_col3],
Table[inclusion_col4],
Table[inclusion_col5]
),
[inclusion1]
&& [inclusion2]
&& [inclusion3]
&& [inclusion4]
&& [inclusion5]
)
VAR start_date =
CALCULATE (
MAX ( Table[startDate] ),
filtertable
)
VAR end_date =
CALCULATE (
MAX ( Table[endDate] ),
filtertable
)
RETURN
DATEDIFF (
start_date,
end_date,
HOUR
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat , I used following statement inside the measure to filter the data:
Var filter_data =
This is what I was suggesting, although I am not clear about your "inclusion" terms. Is that shorthand? What is the actual text for [inclusion1], [inclusion2], ...?
NewMeasure =
VAR filtertable =
FILTER (
ALL (
Table[inclusion_col1],
Table[inclusion_col2],
Table[inclusion_col3],
Table[inclusion_col4],
Table[inclusion_col5]
),
[inclusion1]
&& [inclusion2]
&& [inclusion3]
&& [inclusion4]
&& [inclusion5]
)
VAR start_date =
CALCULATE (
MAX ( Table[startDate] ),
filtertable
)
VAR end_date =
CALCULATE (
MAX ( Table[endDate] ),
filtertable
)
RETURN
DATEDIFF (
start_date,
end_date,
HOUR
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You could first store your Filter table in a variable too, and then use it inside each CALCULATE in the other measures. That way, you are only creating that table once. Also, you should avoid Filtering the whole "Table". Instead you should FILTER(ALL(Table[Column1], Table[Column2], ...) including only the columns on which you are evaluating inclusion criteria.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.