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,
I have a list containing Order Number and Materials, where I need to figure out how many orders have the same pair of materials.
I've solved this using a Basket Analysis which works, where I put the measure in a table, with Materials_Primary and Materials_Filter.
However, I need to correct the measure, such that I don't get a count when Materials_Primary = Materials_Filter.
I.e. I don't want a count for 2002489 / 2002489, but I do want a count for 3024240 / 2002489.
How do I correct the measure to achieve this?
Used measure:
Orders with both products = CALCULATE ( DISTINCTCOUNT ( 'Sales'[Order number] ); CALCULATETABLE ( SUMMARIZE ( 'Sales'; Sales[Order number] ); ALL ( 'Sales' ); USERELATIONSHIP ( 'Sales'[Materials]; Materiale_Filter[Materials] ) ) )
Output example:
Used Model:
Solved! Go to Solution.
Hi @Anonymous
Add EXCEPT ( Materiale_Filter; Material ) to CALCULATETABLE - see below.
By the way, I would have expected ALL ( Materiale ) rather than ALL ( 'Sales' ) in the standard Basket Analysis measure pattern, as you would clear filters other than Materiale. But if it works for you that's fine
Orders with both products = CALCULATE ( DISTINCTCOUNT ( 'Sales'[Order number] ); CALCULATETABLE ( SUMMARIZE ( 'Sales'; Sales[Order number] ); ALL ( 'Sales' ); USERELATIONSHIP ( 'Sales'[Materials]; Materiale_Filter[Materials] ); EXCEPT ( Materiale_Filter; Materiale ) ) )
s
Hi @Anonymous
Add EXCEPT ( Materiale_Filter; Material ) to CALCULATETABLE - see below.
By the way, I would have expected ALL ( Materiale ) rather than ALL ( 'Sales' ) in the standard Basket Analysis measure pattern, as you would clear filters other than Materiale. But if it works for you that's fine
Orders with both products = CALCULATE ( DISTINCTCOUNT ( 'Sales'[Order number] ); CALCULATETABLE ( SUMMARIZE ( 'Sales'; Sales[Order number] ); ALL ( 'Sales' ); USERELATIONSHIP ( 'Sales'[Materials]; Materiale_Filter[Materials] ); EXCEPT ( Materiale_Filter; Materiale ) ) )
s
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |