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 all,
I'm working on a problem that seems like it should have a simple answer, but I have had some trouble finding a good way to write out the DAX formula.
I have a dataset consisting of sales and orders for categories A, B, and C. I need to flag each order as consisting of A only, B only, C only, or a combo of any of A/B/C. Is there an easy way to do this?
I've found a similar answer to what I'm looking for on one of these posts, so maybe I can still use this, but I need to be able to perform other calculations (such as total orders, total revenue, AOV, avg units per order, median order value, etc.) for each category. E.g. I need to be able to show total orders for A only, B only, C only, or combo:
I'm currently calculating a separate measure for category A total orders, separate measure for category B total orders, separate measure for category C total orders, etc. but I need to be able to create one measure/column to calculate total orders split out by category, otherwise I'm looking at 50+ measures as you can imagine. It would be much better if I can perform calculations for total orders, total revenue, AOV, etc. on one column.
Here's what I'm using as a calculated column to find orders that contain category A only (repeating the same for category B and category C), so a category A returns as 'A', otherwise returns as 'NO':
Solved! Go to Solution.
Ah, I forgot HASONEVALUE doesn't work on variables.
This should work and be a bit cleaner than all the calculated columns and a SWITCH:
OrderCategory =
VAR Categories =
CALCULATETABLE (
VALUES ( OrderTable[Category] ),
ALLEXCEPT ( OrderTable, OrderTable[Order ID] )
)
RETURN
IF ( COUNTROWS ( Categories ) = 1, Categories, "Combo" )
A sample dataset is almost always useful. Sharing an example .pbix is even better.
I think creating a calculated column like this on OrderTable might be useful:
OrderCategory =
VAR Categories =
CALCULATETABLE (
VALUES ( OrderTable[Category] ),
ALLEXCEPT ( OrderTable, OrderTable[Order ID] )
)
RETURN
IF ( HASONEVALUE ( Categories ), Categories & " Only", "Combo" )
Hi @AlexisOlson ,
Thank you for your quick reply. I tried the above formula but it labeled all orders as 'Combo'. Do you know how I can add a sample .pbix file? I don't see an attachment option - should I publish to PBI Service? I don't think you'd have the ability to see the backend tables if I did that.
Thank you!
Upload it to e.g. DropBox/Google Drive/OneDrive and share the link.
@AlexisOlson Ah, understood - please let me know if you have trouble accessing this file: https://drive.google.com/file/d/1_3ZjQkiw0u8N88YjRbYHW6VFvCvBiizb/view?usp=sharing
@AlexisOlson I was able to figure this out using SWITCH() true logic:
Ah, I forgot HASONEVALUE doesn't work on variables.
This should work and be a bit cleaner than all the calculated columns and a SWITCH:
OrderCategory =
VAR Categories =
CALCULATETABLE (
VALUES ( OrderTable[Category] ),
ALLEXCEPT ( OrderTable, OrderTable[Order ID] )
)
RETURN
IF ( COUNTROWS ( Categories ) = 1, Categories, "Combo" )
@AlexisOlson excellent - this is much cleaner and I really like not have to using those additional calculated columns.
Thank you much!
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 |
---|---|
110 | |
109 | |
88 | |
76 | |
66 |
User | Count |
---|---|
126 | |
112 | |
99 | |
82 | |
73 |