create a measure that combine column values and measures based on a type column

[avcr29]

Hello!,

I need help please, i'm trying to combine, in a new table, data from two tables, where in table 1 i have a column X,  and on table 2 i have a measure called Xmeasure. This two values (X measure and X column) represent the same but the measure is type 1 and the column is type 2. I create a new table where i've used the function union and summerize to combine the columns in common from this two tables (date,type) so i can later make another measure (weigthed average per category) and then a rolling average  based on the date.

So this is what i have:

Table 1

Date (Month)	Type	X column
March	type 1	55%
May	type 1

Table 2

Date (Month)	Type	X measure
March	type 2	-0%
May	type 2	-Infinity

And this is what i want to create:

Table 3 (New Table)

Date (Month)	Type	X
March	type 1	55%
March	type 2	-0%
May	type 1
May	type 2	-Infinity

As you can see, the values of month May are blank (column) or with infinity value (measure) , this will happen because there are other columns (that are not shown here) that are filled, that's why may exists, but this values in particular are not filled yet, tho eventully May is going to be filled. 

i have created a measure to get X  in table 3 like this:

X =SWITCH(FIRSTNONBLANK('Table 3'[Type],1),"type 1",FIRSTNONBLANK('Table 1'[X column],1),"type 2",CALCULATE('Table 2'[X measure]))

but this doesn't bring the correct result. i know that FIRSTNONBLANK function is not the correct one for what i want to do because it will repeat the value of the non blank row and it doesn't distinguish the correct values per month but i don't know what other function could read a column in a measure.

i don't know if there's another formula that i can use to get the result i want. 

Thanks!

[v-shex-msft]

HI @avcr29,

You can try to use following formula to get related result if it works for your scenario:

result =
VAR _currType =
    SELECTEDVALUE ( Table3[Type] )
VAR _currMonth =
    SELECTEDVALUE ( Table3[Date (Month)] )
VAR temp =
    ADDCOLUMNS ( Table2, "X measure", [X measure] )
RETURN
    IF (
        _currType = "type 1",
        LOOKUPVALUE (
            Table1[X column],
            Table1[Date (Month)], _currMonth,
            Table1[Type], _currType
        ),
        IF (
            _currType = "type 2",
            MINX (
                FILTER ( temp, [Date (Month)] = _currMonth && [Type] = _currType ),
                [X measure]
            )
        )
    )

If above not help, please share more detail information about your formulas.

Regards,

Xiaoxin Sheng

[avcr29]

Hi @v-shex-msft,

Thank you for your reply, i've tried the formula and i think it's almost there when i put it in a table the result is like this:

Date(Month)	Type	result
March	type 1	55%
March	type 2	-100%
May	type 1	0%
May	type 2	0%

i think the minx function is the reason why the result in march type 2 is -100% instead of -0%.  That's why i change it a little bit :

result =
VAR _currType =
    SELECTEDVALUE ( Table3[Type] )
VAR _currMonth =
    SELECTEDVALUE ( Table3[Date (Month)] )
VAR temp =
    ADDCOLUMNS ( Table2, "X measure", [X measure] )
RETURN
    IF (
        _currType = "type 1",
        LOOKUPVALUE (
            Table1[X column],
            Table1[Date (Month)], _currMonth,
            Table1[Type], _currType
        ),
        IF (
            _currType = "type 2",
            CALCULATE(if(and([x measure]>-1,[x measure]<0),0,[x measure]),
 FILTER ( temp, Date(month) = _currMonth && [Type] = _currType )
        )
    )

there i get the wright result, tho when i try to use this measure in a tachometer it shows blank, how can fix this?

Thanks!

[v-shex-msft · Accepted solution]

Hi @avcr29,

Nope, this measure is based on specific row content, when you use it in tachometer, measure can't get correspond result for summary records.

Regards,

Xiaoxin Sheng