Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I'd like to create a measure that performs a SumX of an indicator according to the axes I put in my table, for example :
Here the MixEffectSumX measurement is good, the total is the expected number
But here the measurement only works for a specific axis (Item_Category)
My DAX formula : MixEffectSumX = SUMX(VALUES(Article[Item_Category]), [MixEffect])
The idea is that I would like to replace VALUES(Article[Item_Category]) with something that contains all the axes that I put in my table dynamically. Bearing in mind that I have ten or so dimensions and a hundred or so possible axes, the idea is that if I ever add axes in the future, I won't be obliged to modify this measure.
Does anyone have any clues?
Thanks in advance
please share sample data with some more axis you like to switch. will try
Hi, how can i share you my pbi example ?
PBI files upload is not supported here
You can paste sample data here in reply with all possible axis variability.
Ok, here is some sample data that a used to test :
A table Customer :
CustomerID | CustomerCategory | CustomerSubCategory |
1 | Category1 | SubCategory11 |
2 | Category2 | SubCategory21 |
3 | Category1 | SubCategory12 |
4 | Category2 | SubCategory22 |
5 | Category2 | SubCategory21 |
6 | Category3 | SubCategory31 |
7 | Category3 | SubCategory31 |
8 | Category3 | SubCategory32 |
A Table Product :
ProductID | ProductCategory | ProductSubCategory |
1 | Category1 | SubCategory11 |
2 | Category2 | SubCategory21 |
3 | Category1 | SubCategory12 |
4 | Category2 | SubCategory22 |
5 | Category2 | SubCategory21 |
A table Invoice :
ProductID | CustomerID | Turnover |
1 | 1 | 5465 |
1 | 2 | 543 |
1 | 3 | 1213 |
1 | 4 | 154 |
2 | 5 | 5465 |
2 | 1 | 215 |
2 | 2 | 2424 |
2 | 3 | 104 |
2 | 4 | 4052 |
3 | 5 | 720 |
3 | 1 | 87 |
3 | 2 | 2 |
3 | 4 | 42 |
3 | 5 | 48 |
5 | 6 | 2 |
5 | 2 | 8 |
5 | 1 | 27 |
5 | 4 | 827 |
5 | 8 | 2 |
3 | 1 | 782 |
The goal is to have the "correct" total (which is when the measure contains SumX)
With the 5 following dax formulas :
Hi,
@xifeng_L was right. This is not possible with the current data model. you need to restructur the model may be some other to optimiz the numbers of measure to create. Thanks
Based on my experience, there should be no solution to achieve this idea.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |