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,
See the below visual table I have. I'm trying to created a measure where I can remove the "SubSet" column from my visualization and return the maxium of the count column for each Set Category. See example for current and desired.
Current
Set | SubSet | Count |
A | A.1 | 1 |
A | A.2 | 3 |
A | A.3 | 2 |
A | A.4 | 1 |
B | B.1 | 1 |
B | B.2 | 1 |
B | B.3 | 4 |
C | C.1 | 3 |
C | C.2 | 1 |
Desired (remove Subset and have return a value of the max count for reach Set category)
Set | Max of Subset Counts |
A | 3 |
B | 4 |
C | 3 |
Thanks,
Solved! Go to Solution.
Hi,
Try this calculated column formula
=CALCULATE(MAX(Data[Total]),FILTER(Data,Data[customername]=EARLIER(Data[customername])&&Data[SetFamily]=EARLIER(Data[SetFamily])))
Hope this helps.
Hi , can I ask how you wrote the Desire column?
Which measure did you use?
I want to replicate a similar column in my report but I'm not able to do that. Thanks
Hi,
It is not a measure - it is a calculated column formula.
Hi @Hydramh2
Below are 2 versions of calculated columns I used. You would obviously have to swap in the right fields and names for yours but you can see the DAX i used.
CALCULATE(MAX('SET CONTROL HISTORY'[SetTypeCount]),FILTER('SET CONTROL HISTORY','SET CONTROL HISTORY'[customername]=EARLIER('SET CONTROL HISTORY'[customername])&&'SET CONTROL HISTORY'[SETFAMILY]=EARLIER('SET CONTROL HISTORY'[SETFAMILY])))
SUMX(FILTER('SET CONTROL HISTORY','SET CONTROL HISTORY'[customername]=EARLIER('SET CONTROL HISTORY'[customername])&&'SET CONTROL HISTORY'[SetTypeDesc]=EARLIER('SET CONTROL HISTORY'[SetTypeDesc])),'SET CONTROL HISTORY'[Count1])
In the visual for a Table, add the Set field to values. Create this measure:
Max Set Count = MAX(TableData[Count])
Add that measure to the values.
Change the table name "tableData" to whatever your table name is.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans,
The Count column is a count of subset in a table visualization, both those tables are already in visulizations but I need a measure to get the max of each.
Thanks,
Then use MAX(YourCountMeasure)
IF that isn't what you need, then please post sample data or CLEARLY explain what everything is.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI tried to attach a dummy data set but apparently I cannot since I'm only a regular forum user. Below is a pivot from my dataset replicating a table visulization (dummied down since my dataset is really thousands of lines). I basically want to create either a calculated measure or column that will give me the max of the "Total" column by SetFamily per customer from looking at the setdescription.
I want the largest of the total # from the set descriptions under each set family by customer.
I just don't know the perfect DAX to get to this. Once I have that calculation I want to remove the setdescription from my visual so it only shows the Customer, SetFamily & Max Count column I just have those in there so you can understand my desired result based on the data.
Let me know if this makes sense.
customername | SetFamily | SetDescription | Total | Desire Max result for setfamily by setdescription |
Customer1 | IC1 | IC1 SET 6 | 3 | 3 |
Customer1 | IC1 | IC1 SET1 | 2 | 3 |
Customer1 | IC1 | IC1 SET2 | 2 | 3 |
Customer1 | IC1 | IC1 SET3 | 2 | 3 |
Customer1 | IC1 | IC1 SET4 | 3 | 3 |
Customer2 | AVA | AVA SET | 3 | 3 |
Customer2 | BAT3 | BAT SET5 | 3 | 3 |
Customer2 | IC1 | IC1 SET 6 | 3 | 6 |
Customer2 | IC1 | IC1 SET1 | 3 | 6 |
Customer2 | IC1 | IC1 SET2 | 2 | 6 |
Customer2 | IC1 | IC1 SET3 | 3 | 6 |
Customer2 | IC1 | IC1 SET4 | 6 | 6 |
Customer2 | NCP | NCP SET 1 | 3 | 3 |
Customer2 | NCP | NCP SET 2 | 1 | 3 |
Customer2 | NCP | NCP SET 3 | 1 | 3 |
Customer2 | NSD | NSD SET 1 | 3 | 3 |
Customer2 | NSD | NSD SET 2 | 1 | 3 |
Customer2 | TLX | TLX SET 1 | 3 | 3 |
Thanks,
Hi @chudson , can I ask you how you wrote the Desire column?
Which measure did you use?
I want to replicate a similar column in my report but I'm not able to do that. Thanks
Hi,
Try this calculated column formula
=CALCULATE(MAX(Data[Total]),FILTER(Data,Data[customername]=EARLIER(Data[customername])&&Data[SetFamily]=EARLIER(Data[SetFamily])))
Hope this helps.
Hi @Ashish_Mathur,
Yeah this worked as a calculated column. Is that the only way or is there a measure that can be done to get this result?
Hi,
Try these measures
Measure=SUM(Data[Total])
Max=MAXX(ALL(Data[SetDescription]),[Measure])
Hope this helps.
If your visual has Customer and Set in it, then just =MAX(tablename[Total]) will work as Customer and Set will provide the filter context for it.
If not then you'd need to do something along the lines of this (typing free hand here, so check the syntax)
= CALCULATE( MAX(tablename[Total], ALLEXCEPT(tablename,[Customer],[Set]) )
No matter what you put in the table visual now, it will get the MAX value of TOTAL taking customer and set into account, but ignore anything else.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |