Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi. I have the following measure [Measure 2], which calculates the values for Measure 1 based using values in Table, filtered by measures in a disconnected date table ([RefPeriodEnd] and [RefPeriodStart] reference the disconnected date table).
Measure2:=CALCULATE(
[Measure1],
FILTER(
ALL(Table),
Table[Date] <= [RefPeriodEnd]
&& Table[Date] >= [RefPeriodStart])
)
This works fine.
I now need to add one more filter that will allow me to omit data points where values of Table[Datapoint] are in Table2[Datapoint].
Table2 is simply a list of the data points I want to omit.
I have read that the CONTAINS function might accomplish this, but I am having trouble getting the syntax right with the code I already have. I also tried adding a Calculated Column that returns TRUE or FALSE if the Table1 data point value is in Table 2 and add "&& ALL_Env[FilterOut]=FALSE())" to the filter statement. But Filter does not allow evaluation on True/False, apparently.
Any suggestions?? Thanks for your help.
Solved my own issue, though there is perhaps a better way. I used the ALLEXCEPT() command, allowing me to still filter by the "FILTEROUT" column which has True/False values.
Measure2:=CALCULATE(
[Measure1],
FILTER(
ALLExcept(Table, Table[FilterOut]),
Table[Date] <= [RefPeriodEnd]
&& Table[Date] >= [RefPeriodStart])
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |