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.
Hi,
My data structure looks like this, SKU3 will contain all 3 levels.
SKU1(Parent) | SKU2(Child1) | SKU3(Child2) |
HTC1 | HTC1 | HTC1 |
Apple1 | Apple1 | Apple1 |
Apple1 | Apple1 | Apple4 |
Apple1 | Apple1 | Apple5 |
Samsung1 | Samsung2 | Samsung3 |
Creating a hierarchy and placing in the slicer gives this
Can this be showed where the value doesnt repeat itself?
thanks
Solved! Go to Solution.
Hi @PBIfanatic, it can be done a few ways, one of which is by creating a calculated column and just using an IF statement. For exmaple, the below two calculated columns will get you what you want:
Child_1 = IF ( PBI_Table2[SKU2(Child1)] = PBI_Table2[SKU1(Parent)] , "" , PBI_Table2[SKU2(Child1)] )
Child_2 = IF ( PBI_Table2[SKU3(Child2)] = PBI_Table2[SKU1(Parent)] , "" , PBI_Table2[SKU3(Child2)] )
To achieve your example, I didn't need to bring in Child_1, however, if you are to add many more rows, it is likely you will need to bring them in.
Also, just as an FYI, if you have three specific columns such as that which you have presented as an example (i.e. Parent, Child1, Child2), you could use Power Query to Unpivot your tables and create the following very easily (however, it may not be what you want?).
Anyway, hope one or the other help 🙂
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi @PBIfanatic, it can be done a few ways, one of which is by creating a calculated column and just using an IF statement. For exmaple, the below two calculated columns will get you what you want:
Child_1 = IF ( PBI_Table2[SKU2(Child1)] = PBI_Table2[SKU1(Parent)] , "" , PBI_Table2[SKU2(Child1)] )
Child_2 = IF ( PBI_Table2[SKU3(Child2)] = PBI_Table2[SKU1(Parent)] , "" , PBI_Table2[SKU3(Child2)] )
To achieve your example, I didn't need to bring in Child_1, however, if you are to add many more rows, it is likely you will need to bring them in.
Also, just as an FYI, if you have three specific columns such as that which you have presented as an example (i.e. Parent, Child1, Child2), you could use Power Query to Unpivot your tables and create the following very easily (however, it may not be what you want?).
Anyway, hope one or the other help 🙂
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Thanks @TheoC for the explanation.
Thats very helpful.
Its closer to what I want. There are blanks at each level where the parent is the child. Is it possible to get rid of the blanks in the slicer? that would be best if possible.
I have seen a hierarchy slicer visual which can control the blanks but its not performant.
@PBIfanatic, there are a few ways to achieve this:
1. I believe the best way is through the use of Power Query being the second example I provided.
2. You can untick "blanks" from the Filter pane of the visual when you click on "Child_2" field.
3. Finally, when you add "values" to the Matrix visual, you can use the Filter pane on the visual, click on the values, and then use "Basic Filtering" to untick 'blank'.
Best of luck! 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |