Home Forums Power Pivot Measure that wont function as a value

This topic contains 8 replies, has 3 voices, and was last updated by  tomallan 4 months ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
    Posts
  • #10152

    tech2188
    Participant
    • Started: 1
    • Replies: 3
    • Total: 4

    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.
    #10154

    tech2188
    Participant
    • Started: 1
    • Replies: 3
    • Total: 4

    pic 2

    Attachments:
    You must be logged in to view attached files.
    #10156

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    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.

    Tom

    #10164

    tech2188
    Participant
    • Started: 1
    • Replies: 3
    • Total: 4

    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.

    #10165

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    tech2188,

    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.

    Tom

    #10166

    tech2188
    Participant
    • Started: 1
    • Replies: 3
    • Total: 4

    that does work. Thanks for fully explaining it, that is what i needed both formula and information wise.

    #10167

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Glad to hear the measure works!

    I also modified the second paragraph in my last post to hopefully explain a little better.

    #10169

    pordwalg
    Participant
    • 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.

    #10174

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    pordwalg,

    This is possible, but you need to create your own topic on

    Power Pivot

    Tom

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.