July 9, 2018 at 6:21 pm #10152
I have a measure that wont function as a value. Im attempting to use it as a filter in a custom calendar table.
However, if i type in the value shown manually it works…. what am i doing wrong is this a bug?
Attachments:You must be logged in to view attached files.July 9, 2018 at 6:21 pm #10154
Attachments:You must be logged in to view attached files.July 9, 2018 at 8:15 pm #10156
It is not a bug. Your measure within the grid is returning a value based on the filter you have set on the table, however, in your formula, the filter applied is a single row that is currently processed by the FILTER function.
FILTER is an iterator and applies it’s criteria one row at a time.
If you are using Excel 2016, you probably could get around the iterator by using variables with something like:
Start of Last Month := VAR vM1 = [Measure1] RETURN CALCULATE ( LASTDATE ( Rolling_Calendar_2017on[Date_Rolling] ), FILTER ( VALUES ( Rolling_Calendar_2017on ), Rolling_Calendar_2017on[ISO_YrMo] < vM1 ) )
There is most likely a simpler way to write your formula.
TomJuly 10, 2018 at 12:41 pm #10164
Tom i dont think you looked very closely at the 2 pictures and what was different. the measure was NOT returning a value based on the filter i had set on the table. that is the problem (see picture 1) picture 2 is where i type in the value manually and get the correct result.July 10, 2018 at 2:06 pm #10165
I looked exactly at both pictures and that was the reason I suggested the use of a variable which could bypass the “current row” (iterated row) filter created by the FILTER function and be more **like** the hard-coded value. Variables are only assigned once (when created), but your use of a measure inside of a FILTER is re-evaluated for every iterated row.
Here is the subtlety: the filter at the source of the problem was not just the one you had visibly set, but rather it also included the unseen effect of an iterator using the current row as an additional filter. The result of the measure you see in the grid is not the same result of the identical measure used inside of another formula.
If the formula I suggested doesn’t work, we could look at another formula or make a change in your data model. To do so, please attach some sample data in a workbook.
TomJuly 10, 2018 at 3:57 pm #10166
that does work. Thanks for fully explaining it, that is what i needed both formula and information wise.July 10, 2018 at 8:36 pm #10167
Glad to hear the measure works!
I also modified the second paragraph in my last post to hopefully explain a little better.July 12, 2018 at 5:37 am #10169
- Started: 0
- Replies: 1
- Total: 1
Hello Sir, I need your help.
I am new to Power BI but good in excel so far, I have a table that has defects for a particular aircraft with unique reference number where i have raised flight and compliance flight , whereas i have one another table that has all flights for all aircraft.
I have to have all flights between raised flight and compliance flight so i can know how many flights where affected .
Please assist me , I was using a oracle procedure earlier but that not a table saved so please let me know how can i achieve the above.July 13, 2018 at 6:01 am #10174
You must be logged in to reply to this topic.