Forum Discussion

CTan42's avatar
CTan42
Icon for Helper II rankHelper II
9 years ago

same measure in every pages

Hi all, I have 5 tables (e.g. Table A, Table B, Table C, Table D, Table E). Below is the measure which i want to do: 1. CurrentMonth = sum('Table A'[Revenue]) 2. CurrentMonth = sum('Table B'[Revenu...
Need Help
cs_skit's avatar
cs_skit
Icon for Resolver IV rankResolver IV
9 years ago

You don't need the measure in TableA, B, C, D and E if you do a SUM over all and filter according to your relations like with a slicer.

 

You could even do a slicer on fixed values A/B/C/D/E if you really really need it the way you said. 

 

Measures are not really stored in tables that is just for visual/order purposes. The context of the measure and like which tables it references is defined only by the DAX.

Just place it in TableA or wherever seems best and you can use it anywhere.

  • CTan42's avatar
    CTan42
    Icon for Helper II rankHelper II
    9 years ago

    Thanks for the suggestion. However I dont really understand what you are saying. But the reason why i need to do the measure is I have to make a matrix table at the end with using the measure as the value in the matrix table. Or could show me the instruction?

    Thanks

    • cs_skit's avatar
      cs_skit
      Icon for Resolver IV rankResolver IV
      9 years ago

      Maybe if you post your tables and what you need peoples can propose a solution.

       

      Btw if its the same data structure in those tables you might want to merge them. Either in Query Editor or by New Table DAX

       

      NewTableName =

      UNION(

      TableA; TableB; TableC; TableD; TableE

      )

       

      What you want in your original question is impossible each measure has to have unique name. But ihmo its the wrong question there is probably a better solution for your problem then what you want to do in your first post.

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi CTan42,

      As the cs_skit said, you can create a disconnected table as follows. Create a slicer including [TableName].



      Then create a measure using the formula.

      CurrentMonth = SWITCH(CALCULATE(FIRSTNONBLANK(NewTable[TableName],NewTable[TableName]),ALLSELECTED(NewTable)),
                      "A", sum('Table A'[Revenue]),
                      "B", sum('Table B'[Revenue]),
                      "C", sum('Table C'[Revenue]),
                      "D", sum('Table D'[Revenue]),
                      "E", sum('Table E'[Revenue]), 
)


      When you select "A" in slicer, it will calculate the sum('Table A'[Revenue]), when you select "B", it will calculate sum('Table B'[Revenue]) and so on.

      More details, please review this article.

      Best Regards,
      Angelia

       

      • CTan42's avatar
        CTan42
        Icon for Helper II rankHelper II
        9 years ago

        v-huizhn-msft thanks for the suggestion. However, my data is complicated. The reason why I do this measure is i have to use this measure into another measure. Below are my full measure for 1 table:

        1. CurrentMonth = SUM('A'[Revenue])

        2. PreviousMonth = CALCULATE('A'[CurrentMonth], PREVIOUSMONTH('A'[Date]))

        3. LastYearMonth = CALCULATE('A'[CurrentMonth], SAMEPERIODLASTYEAR('A'[Date]))

        4. CurrentMonth/PreviousMonth = 'A'[CurrentMonth] - 'A'[PreviousMonth]

        5. CurrentMonth/LastYearMonth = 'A'[CurrentMonth] - 'A'[LastYearMonth]

         

        As power bi doesnt allow me use the same measure name, so atm i using different measure name in every table (eg. A<CurrentMonth>, B<CurrentMonth>).  What I hope to do is just have all measure are having the same name. Is there anyway to make it better?

         

        thanks.