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.
Hello!
My Problem:
I have a table with 12 calculated measure values. Now I want this 12 measure show in a line diagram. I want to create a new table with 2 columns (month, calculated measures values). Is it possible to give a cell a direct Link to a measure (like Slink)? Or how can I solve this problem?
Thanks
Solved! Go to Solution.
Hi @Psycho,
You can open the query editor and use "Unpivot Columns" function to work through the issue.
Sample:
Use duplicate function to backup this table:
Choose month columns and click on "Unpivot Columns" to merge them:
Result:
After above steps, you can continue the unfinished work.
Regards,
Xiaoxin Sheng
Yeah, that is a good solution. But there is my next problem. 🙂
now I have to do completly new calculations. (a lot of them). Do you have a Idea?
I want a calculate like this:
02 Dur.Bestmge.AktJ = if(Tabelle2[Monat] = "End.Best.Mge.AktJ.02" ; ....
.... (the value [Bestand] of this row + the value [Bestand] of that row, with the same value of the column [Artikelnummer] and the value "End.Best.Mge.AktJ 01")/2
this is harder than the other problem ^^
Youre idea with:
"SWITCH([Date].[MonthNo],1,[Sum of Jan],2,[Sum of Feb],3,[Sum of Mar],0))"
is very good and it worked. But unfortunately does not react to filter in the first table. Its ever the completly value. Now I must unpivot. 😞
Hi @Psycho,
You can use measure to instead the calculated column Spalte, and it will works on slicer:
Measure:
Spalte(dymic) = SWITCH(MAX([Refer.Wert]), 1, [01 LUG.AktJ], 2,[02 LUG.AktJ], 3,[03 LUG.AktJ], 4,[04 LUG.AktJ]
, 5, [05 LUG.AktJ], 6, [06 LUG.AktJ], 7, [07 LUG.AktJ], 8, [08 LUG.AktJ]
, 9, [09 LUG.AktJ], 10, [10 LUG.AktJ], 11, [11 LUG.AktJ], 12, [12 LUG.AktJ]
, 0)
Regards,
Xiaoxin Sheng
Hi @Psycho,
According to your description, you want to get the monthly summary table, right?
If as I said, you can refer to below steps:
Sample table:
Calculate table formula(index column is use to sort the visual):
Table = DISTINCT(SELECTCOLUMNS(Sheet1,"Month",[Date].[Month],"Amount",SUMX(FILTER(ALL(Sheet1),Sheet1[Date].[Month]=EARLIER(Sheet1[Date].[Month])),Sheet1[Amount]),"Index",[Date].[MonthNo]))
Create visual:
>>Is it possible to give a cell a direct Link to a measure (like Slink)?
You can use switch function to choose the value of measure, for example(I have created three measures to calculate the specific month amount):
Table 2 = DISTINCT(SELECTCOLUMNS(Sheet1,"Month",[Date].[Month],"Measure value",SWITCH([Date].[MonthNo],1,[Sum of Jan],2,[Sum of Feb],3,[Sum of Mar],0)))
Regards,
Xiaoxin Sheng
Hello, Xiaxin! Thanks to you for your approach. The problem is, my starting table has no date column or any date values. But 12 of 20 column contains monthly observation values. So I can not make a direct connection to Sheet1 with a date. From these 12 columns, (and a lot of calculated columns) I I calculate 12 values. One per month. And this values must in a Line Diagram.
Hi @Psycho,
You can open the query editor and use "Unpivot Columns" function to work through the issue.
Sample:
Use duplicate function to backup this table:
Choose month columns and click on "Unpivot Columns" to merge them:
Result:
After above steps, you can continue the unfinished work.
Regards,
Xiaoxin Sheng
Yeah, that is a good solution. But there is my next problem. 🙂
now I have to do completly new calculations. (a lot of them). Do you have a Idea?
I want a calculate like this:
02 Dur.Bestmge.AktJ = if(Tabelle2[Monat] = "End.Best.Mge.AktJ.02" ; ....
.... (the value [Bestand] of this row + the value [Bestand] of that row, with the same value of the column [Artikelnummer] and the value "End.Best.Mge.AktJ 01")/2
this is harder than the other problem ^^
Youre idea with:
"SWITCH([Date].[MonthNo],1,[Sum of Jan],2,[Sum of Feb],3,[Sum of Mar],0))"
is very good and it worked. But unfortunately does not react to filter in the first table. Its ever the completly value. Now I must unpivot. 😞
Hi @Psycho,
Sorry for slow response, you can try to use the merged column to calculate.(But I think unpivot not a effective way, it will create a lot of Redundant data)
>>"But unfortunately does not react to filter in the first table"
It not appeared on my side, can you share me a simple sample, I will test it.
Regards,
Xiaoxin Sheng
Yes of curse. Here e sample. I want to show the values "Monats LUG" in a Linediagram. It works for the unfiltered falue.
http://www.file-upload.net/download-12038951/TestLUG2-Kopie.pbix.html
Hi @Psycho,
Can you share it to 1drive?
Regards,
Xiaoxin Sheng
Hi @Psycho,
You can use measure to instead the calculated column Spalte, and it will works on slicer:
Measure:
Spalte(dymic) = SWITCH(MAX([Refer.Wert]), 1, [01 LUG.AktJ], 2,[02 LUG.AktJ], 3,[03 LUG.AktJ], 4,[04 LUG.AktJ]
, 5, [05 LUG.AktJ], 6, [06 LUG.AktJ], 7, [07 LUG.AktJ], 8, [08 LUG.AktJ]
, 9, [09 LUG.AktJ], 10, [10 LUG.AktJ], 11, [11 LUG.AktJ], 12, [12 LUG.AktJ]
, 0)
Regards,
Xiaoxin Sheng
Thank you so much. I learned a lot from you
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |