Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I used to create a month on month progression of orderd ID's ordered in specific month and i used to get everything in a triangular table as shown in the screenshot ( 1st triangle data "refer screenshot") but looking at it we don't get the clear visualization so we used to invert the triangle (2nd triangle data "refer screenshot") and all the inversion is being done in excel by shifting the values to the left.
To get inversion triangle in powerbi i tried by creating the new columns for every month by writing the dax code as shown below.
i wnated to know is it possible for triangular inversion without writing dax code if it is not possible how do we do that by writing the dax code without creating the new columns or measures like 1,2,3,4,5.... .
The outcome expecting should be the same as second triangle
Here is the screenshot for reference
Solved! Go to Solution.
Hi @Baji_Komara ,
Please try:
First create a new table and unpivot the data:
Output:
Then create measures like these:
1 =
VAR _a =
MAX ( 'Table'[fin_mon] )
VAR _b =
SWITCH (
_a,
1, "April",
2, "Upto May",
3, "Upto JUne",
4, "upto july",
5, "Upto Aug",
6, "Apto Sep",
7, "Upto Oct"
)
RETURN
CALCULATE (
MAX ( 'Table (2)'[Value] ),
FILTER ( 'Table (2)', [Attribute] = _b )
)
2 =
VAR _a =
MAX ( 'Table'[fin_mon] ) + 1
VAR _b =
SWITCH (
_a,
1, "April",
2, "Upto May",
3, "Upto JUne",
4, "upto july",
5, "Upto Aug",
6, "Apto Sep",
7, "Upto Oct"
)
RETURN
CALCULATE (
MAX ( 'Table (2)'[Value] ),
FILTER ( 'Table (2)', [Attribute] = _b )
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Baji_Komara ,
1. You can try to umpivot your data and add them into a matrix visual, then create a measure for them.
2. If you want the calculayed columns in power query editor, you need to create the columns in power query editor(using M code), the columns you created using DAX won't appear in query editor.
3. You can't unpivot the columns using DAX directly. If you want to unpivot them using DAX you may need to create a new table(using DAX). The easiest way is using unpivot functioon in power query editor.
Besides, these questions are beyond the topic discussed at the beginning of this thread. If the replies in the thread are helpful to you, please consider marking the replies that are helpful to you and creating a new thread for each question, which will make the thread more relevant and allow more people to help you. And other people with similar problems will be better able to get help from the post.
Thanks in advance!
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Baji_Komara ,
1. You can try to umpivot your data and add them into a matrix visual, then create a measure for them.
2. If you want the calculayed columns in power query editor, you need to create the columns in power query editor(using M code), the columns you created using DAX won't appear in query editor.
3. You can't unpivot the columns using DAX directly. If you want to unpivot them using DAX you may need to create a new table(using DAX). The easiest way is using unpivot functioon in power query editor.
Besides, these questions are beyond the topic discussed at the beginning of this thread. If the replies in the thread are helpful to you, please consider marking the replies that are helpful to you and creating a new thread for each question, which will make the thread more relevant and allow more people to help you. And other people with similar problems will be better able to get help from the post.
Thanks in advance!
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-jianboli-msft ,
Thanks for your response. yeah i would be creating new threads from next time onwards which would help many people
hi @v-jianboli-msft ,
Thanks for your response. i have three queries now
Hi @Baji_Komara ,
Please try:
First create a new table and unpivot the data:
Output:
Then create measures like these:
1 =
VAR _a =
MAX ( 'Table'[fin_mon] )
VAR _b =
SWITCH (
_a,
1, "April",
2, "Upto May",
3, "Upto JUne",
4, "upto july",
5, "Upto Aug",
6, "Apto Sep",
7, "Upto Oct"
)
RETURN
CALCULATE (
MAX ( 'Table (2)'[Value] ),
FILTER ( 'Table (2)', [Attribute] = _b )
)
2 =
VAR _a =
MAX ( 'Table'[fin_mon] ) + 1
VAR _b =
SWITCH (
_a,
1, "April",
2, "Upto May",
3, "Upto JUne",
4, "upto july",
5, "Upto Aug",
6, "Apto Sep",
7, "Upto Oct"
)
RETURN
CALCULATE (
MAX ( 'Table (2)'[Value] ),
FILTER ( 'Table (2)', [Attribute] = _b )
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |