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 am a newbei to DAX.I have two tables as follows:
customerID | customername |
1 | aaa |
2 | bbb |
3 | ccc |
ProductID | SalesCustomerID | Range | Quantity |
1000 | 3 | H | 10 |
1004 | 3 | C | 15 |
1009 | 3 | ||
2000 | 1 | K | 20 |
1007 | 1 | C | 25 |
1002 | 1 | ||
1005 | 2 | A | 16 |
1003 | 2 | K | 18 |
I want to populate the Range per customer where the Quantity is the highest.Th expected output is :
customerID | customername | Highestrange |
1 | aaa | C |
2 | bbb | K |
3 | ccc | C |
Thank you in Advance!!!
Solved! Go to Solution.
Hi @Anonymous
You can create a Measure as the following.
HighestRange =
VAR max__ =
MAXX (
FILTER (
Quantity,
Quantity[SalesCustomerID] = MAX ( Quantity[SalesCustomerID] )
),
Quantity[Quantity]
)
RETURN
CALCULATE ( MAX ( Quantity[Range] ), Quantity[Quantity] = max__ )
The result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi @Anonymous
You can create a Measure as the following.
HighestRange =
VAR max__ =
MAXX (
FILTER (
Quantity,
Quantity[SalesCustomerID] = MAX ( Quantity[SalesCustomerID] )
),
Quantity[Quantity]
)
RETURN
CALCULATE ( MAX ( Quantity[Range] ), Quantity[Quantity] = max__ )
The result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
@Anonymous , Create a new measure like this and use that in visual
Highestrange =
VAR __id = MAX ('Table'[SalesCustomerID] )
VAR __date = CALCULATE ( MAX('Table'[Quantity] ), ALLSELECTED ('Table' ), 'Table'[SalesCustomerID] = __id )
CALCULATE ( MAX ('Table'[Range] ), VALUES ('Table'[SalesCustomerID] ),'Table'[SalesCustomerID] = __id,'Table'[Quantity] = __date )
Thank you for your response!!! But I am getting the output for customer bbb highestrange = C, where as am expecting K
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |