Help with Measures

[Chandrashekar]

Hello Team,

In attached file Sample Report I have two measures. is it possible to combine Measure 2 with Measure 1.

Regards,

Chandrashekar B

[AlexisOlson]

How about this?

Combined = 
SUMX (
    SUMMARIZE ( Table3, Table3[Name], Table3[Location] ),
    CALCULATE (
        COUNTROWS ( FILTER ( Table1, Table1[Priority] = 1 ) ),
        Table1[Name] = EARLIER ( Table3[Name] ),
        Table1[Location] = EARLIER ( Table3[Location] )
    )
)

[Chandrashekar]

Hello Alex,

It is working fine but not working if i use Treatas. Any advise on it.

_countrows =
VAR _name =
MAX ( Table3[Name] )
VAR _location =
MAX ( Table3[Location] )
VAR _tbl =
CROSSJOIN (
SELECTCOLUMNS ( { _name }, "name", [Value] ),
SELECTCOLUMNS ( { _location }, "location", [Value] )
)
RETURN
SUMX(
SUMMARIZE(Table3,Table3[Name],Table3[Location]),
CALCULATE (
COUNTROWS ( FILTER ( Table1, Table1[Priority] = 1 ) ),
TREATAS ( _tbl, Table1[Name], Table1[Location] )

))

Regards,

Chandrashekar B

[AlexisOlson · Accepted solution]

Try putting your variables inside the SUMX.

_countrows = 
SUMX (
    SUMMARIZE ( Table3, Table3[Name], Table3[Location] ),
    VAR _tbl =
        CROSSJOIN (
            SELECTCOLUMNS ( { Table3[Name] }, "name", [Value] ),
            SELECTCOLUMNS ( { Table3[Location] }, "location", [Value] )
        )
    RETURN
        CALCULATE (
            COUNTROWS ( FILTER ( Table1, Table1[Priority] = 1 ) ),
            TREATAS ( _tbl, Table1[Name], Table1[Location] )
        )
)

[Chandrashekar]

Hello Alex,

Thanks it is working fine.

Regards,

Chandrashekar B

[Chandrashekar]

Hello Alex,

Thank you and will try on it.

Regards,

Chandrashekar B

[amitchandak]

@Chandrashekar , I tried a few combinations. Current two measure solution seems better

 I tried this and it did not work

_countrows = 
VAR _name =
    MAX ( Table3[Name] )
VAR _location =
    MAX ( Table3[Location] )
VAR _tbl =
    CROSSJOIN (
        SELECTCOLUMNS ( { _name }, "name", [Value] ),
        SELECTCOLUMNS ( { _location }, "location", [Value] )
    )
var _final = CALCULATE (
        COUNTROWS ( FILTER ( Table1, Table1[Priority] = 1 ) ),
        TREATAS ( _tbl, Table1[Name], Table1[Location] )
    )
RETURN sumx(ADDCOLUMNS(summarize(Table3,Table3[Name],Table3[Location]),"_1",calculate(_final)),[_1])