Forum Discussion

Chandrashekar's avatar
Chandrashekar
Resolver III
4 years ago
Solved

Help with Measures

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's avatar
    AlexisOlson
    4 years ago

    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] )
        )
)

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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])

     

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      Chandrashekar
      Resolver III
      4 years ago

      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's avatar
        AlexisOlson
        Super User
        4 years ago

        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] )
        )
)