Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I apologize, I have been struggling with this mesure for the last couple of days. I am new to Dax and my expereince with Excel formulas is limited as well which is really hindering me on this problem. I need to do two calculations which end up being the reverse of each other so once I get the one down then the other should be simple.
We have unique Order Numbers for each project we sell. In that project may be Solid Panels or Glass Panels or Both. I want to filter out the ones that have just Glass Panels and no Solid panels and return the number of unique projects that have only Glass panels as a percentage of all projects. I then want to do the opposite and show the number of projects that have both and not just Glass Panels as a percentage of all projects. In the end I want to see the trends on products sold by region by year.
I believe I can use DISTINCTCOUNT for total projects based on Order Number but I can't figure out how to filter based on the values in the rows to show what I need to show
Any advice you are able to share would be much appreciated. Thanks in advance.
Solved! Go to Solution.
Hello @chunkysoup
download the file: https://1drv.ms/u/s!AiiWkkwHZChHj1mOwajb5i1eVvVl
try with the below model:
Only Glass Panels = CALCULATE( COUNTROWS( Orders ), EXCEPT( CALCULATETABLE( VALUES( Orders[Order Number] ), Panels[Panel Type] = "Glass", CROSSFILTER( Data[Order Number], Orders[Order Number], Both ) ), CALCULATETABLE( VALUES( Orders[Order Number] ), Panels[Panel Type] = "Solid", CROSSFILTER( Data[Order Number], Orders[Order Number], Both ) ) ) )
Both Panels = CALCULATE( COUNTROWS( Orders ), INTERSECT( CALCULATETABLE( VALUES( Orders[Order Number] ), Panels[Panel Type] = "Glass", CROSSFILTER( Data[Order Number], Orders[Order Number], Both ) ), CALCULATETABLE( VALUES( Orders[Order Number] ), Panels[Panel Type] = "Solid", CROSSFILTER( Data[Order Number], Orders[Order Number], Both ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello @chunkysoup
download the file: https://1drv.ms/u/s!AiiWkkwHZChHj1mOwajb5i1eVvVl
try with the below model:
Only Glass Panels = CALCULATE( COUNTROWS( Orders ), EXCEPT( CALCULATETABLE( VALUES( Orders[Order Number] ), Panels[Panel Type] = "Glass", CROSSFILTER( Data[Order Number], Orders[Order Number], Both ) ), CALCULATETABLE( VALUES( Orders[Order Number] ), Panels[Panel Type] = "Solid", CROSSFILTER( Data[Order Number], Orders[Order Number], Both ) ) ) )
Both Panels = CALCULATE( COUNTROWS( Orders ), INTERSECT( CALCULATETABLE( VALUES( Orders[Order Number] ), Panels[Panel Type] = "Glass", CROSSFILTER( Data[Order Number], Orders[Order Number], Both ) ), CALCULATETABLE( VALUES( Orders[Order Number] ), Panels[Panel Type] = "Solid", CROSSFILTER( Data[Order Number], Orders[Order Number], Both ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
That worked! For the most part anyway. I had overly simplified it so I made a couple little changes but it works! I think the rest is me playing with the results and understanding what it is actually doing and how. Thank you so much for your help!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
66 | |
55 |