Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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'[Revenue])
3. CurrentMonth = sum('Table C'[Revenue])
4. CurrentMonth = sum('Table D'[Revenue])
5. CurrentMonth = sum('Table E'[Revenue])
So my problem here is i have to do that measure in every tables. Howevery the power bi doesn't allow me to using the same measure name. Due to the presentation purpose, I'm suggest to use 'current month' as the column name. So, is there any other ways to fix it? Thanks.
Regards,
Chung.
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.
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
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
@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.
Hi @CTan42,
Unfortunately, we are unable to use the same name for different measures. Usually, we can not recognize each measure's calculation if they have same name.
Best Regards,
Angelia
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |