Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I all,
I have a table with some data columns
A B C D
10/11/2019 120 132 87 150
11/11/2019 110 121 83 143
…..
and some measures that are always the same formula but referred to different data columns
i.e.
MEASURE 1 = CALCULATE(SUM[A];FILTER(Basedati;(Basedati[Stato]="Cancellazione" && Basedati[Data modifica stato]<=maxlettura) || (Basedati[Stato]="No Show" && Basedati[Dal]<maxlettura)))
MEASURE 2 = CALCULATE(SUM[B];FILTER(Basedati;(Basedati[Stato]="Cancellazione" && Basedati[Data modifica stato]<=maxlettura) || (Basedati[Stato]="No Show" && Basedati[Dal]<maxlettura)))
MEASURE 3 = CALCULATE(SUM[C];….
….
Is there anyway to write just the First Measure and for the others make something like following?
MEASURE 2 = MEASURE 1 but use [B] instead of [A]
MEASURE 2 = MEASURE 1 but use [C] instead of [A]
Thank you very much,
Alessandro
Solved! Go to Solution.
Sorry if I was unclear. What I meant was that if you want to keep your current table structure, there is no other solution than keep on doing what you already are doing, writing a measure for each column. There is no way in Power BI to write that measure once, and then replace the column as you suggest.
An alternative solution would be to reorganize your table(s), by unpivoting your table(s). Depending on your tables it might also be something to gain from splitting them up into several tables before unpivoting.
Hi @ale-hd ,
We can unpivot the table as below.
M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMLRU0lEyNDIAkcZGQNLCXClWByRrBJc1BMsaGYJkjZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, A = _t, B = _t, C = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}})
in
#"Changed Type"
After that, we can create measure as your want and add it to matrix to show the data. For more details, please check the pbix as attached.
Hi @ale-hd,
nope, with you table structure you have to write the code for each measure.
But, I am pretty sure you can reorganize your data so that you the column names as rows. This will allow you do write a single measure that can be reused.
Date ColName Value
søndag 10. november 2019 | A | 120 |
søndag 10. november 2019 | B | 132 |
søndag 10. november 2019 | C | 87 |
søndag 10. november 2019 | D | 150 |
mandag 11. november 2019 | A | 110 |
mandag 11. november 2019 | B | 121 |
mandag 11. november 2019 | C | 83 |
mandag 11. november 2019 | D | 143 |
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Thank you Sturla, unfortunately the solution "you have to write the code for each measure" is the one I am using now and it is really uncomfortable. The real measures I am using are much more complex than the ones I reported in the post and are mnay different ones.
Sorry if I was unclear. What I meant was that if you want to keep your current table structure, there is no other solution than keep on doing what you already are doing, writing a measure for each column. There is no way in Power BI to write that measure once, and then replace the column as you suggest.
An alternative solution would be to reorganize your table(s), by unpivoting your table(s). Depending on your tables it might also be something to gain from splitting them up into several tables before unpivoting.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |