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.
Greetings all. I've got a rather difficult challenge. First off, here's what I'm working with:
The first 2 columns come from a static source and are a quantity of a specific item, and how frequently customers purchase that quantity (ex: Customers bought that item in a quantity of 7 nine times). The third column comes from a measure with a bunch of math behind it that's not particularly important. From there, I created a measure that, for quantity 1-19, it multiplies the frequency * adjustment (where the adjustment is always between 0 and 1). The measure I wrote is:
Hi @JonV,
You can try the formula below. Please refer to dax/summarize-function-dax.
measure = SUMX ( SUMMARIZE ( sample, [Quantity], [Frequency], [Adjustment], "AF", [Adjusted Frequency] ), [AF] )
Best Regards,
This solution will not work. As noted in my OP, it needs to be able to be dropped into the IF function as the 3rd term to complete the blank cell. This function references that entire column which creates a circular dependency as it's trying to fill a cell in the column it's trying to calculate.
I should also note that the "Adjustment" column came from a measure which also complicates what DAX functions can reference it.
Hi @JonV,
Can you share the file? A dummy one is enough. Please mask the sensitive parts.
Best Regards,
I'd love to. But I don't see an option to do so. Do I need to upload it elsewhere?
Hi @JonV,
You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts.
Best Regards,
I've uploaded a simplified file to my Google drive. Please let me know if it works.
https://drive.google.com/open?id=1GCNsAYE4WStfd8pocGcPapeAxblyGQf3
Hi @JonV,
You just need to remove the filter context. Please try the formula below.
Adjusted Frequency = IF ( MIN ( 'Test Frequency'[Quantity] ) < 20, SUMX ( 'Test Frequency', IF ( 'Test Frequency'[Quantity] < 20, 'Test Frequency'[Frequency] * [Adjustment] ) ), CALCULATE ( SUM ( 'Test Frequency'[Frequency] ) - SUMX ( 'Test Frequency', IF ( 'Test Frequency'[Quantity] < 20, 'Test Frequency'[Frequency] * [Adjustment] ) ), ALL ( 'Test Frequency' ) ) )
Best Regards,
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |