Highlight Changes towards previous dataset (in filtered view)

[Anonymous]

Dear Super Users,

for a helpful view of a production environment, I'm asked ot create a measure, that give info about changes from previous to current setting.

Background info: The main table has an ID, that I can of course easily decrease to set a filter like

VAR previous = SELECTEDVALUE([Id]) - 1
VAR content = FILTER(ALL(Table1), Table1[Id] = current || Table2[Id] = previous)

What I'm now stuck with is the following: In a related table, there is the LOT# of compounds used in each production batch, like this:

[PkID]     [Compound]     [Batch]

123         CompName1    A
123         CompName2    C
124         CompName1    B
124         CompName2    D

Targeted results:      "CompName1 changed to B, CompName2 changed to D"

Solution path (so far): Retrieve batches for previous and current production and do an "except" to get the difference

VAR LOTprv = ADDCOLUMNS(CALCULATETABLE(Table2,FILTER(ALL(Table1[Id]),[Id]=previous)),
                                                  "Combined",[CompName] & ">" & [Batch])
VAR LOTcrt = ADDCOLUMNS(CALCULATETABLE(Table2,FILTER(ALL(Table1[ID]),[Id]=current)),
                                                 "Combined",[CompName] & ">" & [Batch])

VAR delta = EXCEPT(LOTcrt,LOTprv)

This gives me currently a table with a single column, in 2 rows:
[Combined]

CompName1 >C

CompName2 >D

Current Issue: I can't get this intermediate table to be accepted in a CONCATENATEX to combine both rows into a scalar

I tried CONCATENATEX(VALUES(delta),delta[Combined], " and "), but either the values function doesn't like the value reference, complaining it awaits a table or column expression or it tells me that it cannot find the column "Combined", which I defined earlier in the ADDCOLUMNS statement.

Any thoughts about this?

[daxer-almighty · Accepted solution]

You should learn how variables in DAX work. They do not work like tables. And on top of this, you should also learn what 'data lineage' in DAX is because your code shows this is a piece of knowledge you're missing.

Your code should be something like this:

Meas = 
// Current will be blank if there are more
// than 1 [Id]'s visible and Previous will
// return -1.
var Current = SELECTEDVALUE( [Id] )
VAR Previous = Current - 1
VAR PreviousLot = 
    SELECTCOLUMNS(
        // You should never force the engine
        // to work harder than in should.
        // Do not do EXCEPT on a whole table
        // if you don't have to. Use only those
        // columns that make sense from the business
        // perspective. This way your DAX will be
        // faster.
        CALCULATETABLE(
            Table2,
            [Id] = Previous
        ),
        "@Combined",
            // Columns must always be preceded by
            // their table's name. ALWAYS. Measures
            // on the other hand should NEVER be.
            Table2[CompName] & ">" & Table2[Batch]
   )
VAR CurrentLot = 
    SELECTCOLUMNS(
        CALCULATETABLE(
            Table2,
            [Id] = Current
        ),
        "@Combined",
            Table2[CompName] & ">" & Table2[Batch]
    )
VAR Delta = 
    EXCEPT( CurrentLot, PreviousLot )
var Output =
    CONCATENATEX(
        Delta,
        [@Combined],
        ", ",
        [@Combined]
    )
return
    Output

[Anonymous]

Good morning daxer-almighty,

this seriously saved my day! I'm a chemist by profession and slowly making my way into DAX...I know try to understand why your code works:
The selectcolumns I can well understand - reduce data load at all times.
I guess the "@" to the name is more about esthetics then function?
Following your comments on "w/ or w/o tablename preceeding" I conclude that any "derived figure" in a measure, like with Addcolumns/Selectcolumns is treated as "on the fly measure"?

Like mentioned: Kudos!

Your first comment is already taken care of. I might have also used "SELECTEDVALUE", but this worked for me:
VAR current = IF(COUNT(Table1[Id]) = 1,VALUES(Table1[Id],BLANK())
....
RETURN IF(current=blank(),blank(),output)

[daxer-almighty]

Hi @Anonymous 

Yes, it's a convention in DAX to name columns calculated on the fly starting with "@". This way one can easily distinguish between them and the regular ones. Secondly, measures are never derived on the fly. They are defined in the model and you can't define a measure in a different way. 

And lastly... this is what you should have written:

VAR current = IF( HASONEVALUE( Table1[Id] ), VALUES( Table1[Id] ) )

// you don't need the false-branch condition since BLANK is the default
....
RETURN IF( NOT IS BLANK( current ), output )

// You have to be careful with such comparisons like the one above

// since 0 = blank and (an empty string) "" = blank as well. If you want

// to check for a real blank, then you either use the function ISBLANK

// or use the double-equality sign: x == BLANK().