How to change the data reference of a measure

[ale-hd]

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

[v-frfei-msft]

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.

[ale-hd]

Kudos for the time you dedicate to me, thank you, unfortunately I cannot split the table.

[sturlaws]

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.

[ale-hd]

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.

[sturlaws · Accepted 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.