Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hello All,
I have a requirement to create a DAX measure to calculate to show the max customers count by grouping them on date and to display the corresponding day with max customers count. I am using directy query and not storing any data inside Power BI
Below is the dummied data table to illustrate my scenario.Here, i wanted to calcualate the sum of customers count by grouping them on Date column, then display the date with maxium customers count and also want to display the max customers count.
I have also provided the table after grouping by date. At the end, i wanted to display the max customers count (946) and the corresponding date (11/13/2019).
I would appreciate any suggestions or feedback to get the max customers count and the corresponding date field using DAX measure in Direct Query.
Fact Table
Date | Customers Count |
11/1/2019 | 230 |
11/1/2019 | 150 |
11/1/2019 | 245 |
11/2/2019 | 165 |
11/2/2019 | 176 |
11/2/2019 | 185 |
11/13/2019 | 56 |
11/13/2019 | 320 |
11/13/2019 | 120 |
11/13/2019 | 450 |
11/24/2019 | 190 |
11/24/2019 | 120 |
11/24/2019 | 340 |
11/24/2019 | 230 |
Table after grouping the data based on date field
Date | Customers Count |
11/1/2019 | 625 |
11/2/2019 | 526 |
11/13/2019 | 946 |
11/24/2019 | 880 |
Thank you!!
Solved! Go to Solution.
Hello @manojsv19 ,
Try this:First measure
Max Count of Customers on one day =
VAR _newTable =
ADDCOLUMNS (
'Table',
"Sum of customers per d", CALCULATE (
SUM ( 'Table'[Customers Count] ),
ALLEXCEPT ( 'Table', 'Table'[ Date] )
)
)
VAR _newTableMax =
MAXX ( _newTable, [Sum of customers per d] )
RETURN
_newTableMax
Created a calculated column
Sum of customers per date = CALCULATE(sum('Table'[Customers Count]),ALLEXCEPT('Table','Table'[ Date]))
Last measure
Date with highest count of customers =
var _maxCount = MAX('Table'[Sum of customers per date])
return
CALCULATE(MAX('Table'[ Date]),FILTER(ALLEXCEPT('Table','Table'[ Date]),'Table'[Sum of customers per date]=_maxCount))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @manojsv19 ,
Figured out the syntax of a the virtual table so that you don't need to create a calculated column. This replaces the last measure.
Date with highest count of customers 2 =
VAR _newTable =
ADDCOLUMNS (
'Table',
"Sum of customers per d", CALCULATE (
SUM ( 'Table'[Customers Count] ),
ALLEXCEPT ( 'Table', 'Table'[ Date] )
)
)
VAR _newTableMax =
MAXX ( _newTable, [Sum of customers per d] )
var _calc = CALCULATE(MAX('Table'[ Date]),FILTER(_newTable,MAX([Sum of customers per d] )= _newTableMax))
RETURN
_calc
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hello @manojsv19 ,
Try this:First measure
Max Count of Customers on one day =
VAR _newTable =
ADDCOLUMNS (
'Table',
"Sum of customers per d", CALCULATE (
SUM ( 'Table'[Customers Count] ),
ALLEXCEPT ( 'Table', 'Table'[ Date] )
)
)
VAR _newTableMax =
MAXX ( _newTable, [Sum of customers per d] )
RETURN
_newTableMax
Created a calculated column
Sum of customers per date = CALCULATE(sum('Table'[Customers Count]),ALLEXCEPT('Table','Table'[ Date]))
Last measure
Date with highest count of customers =
var _maxCount = MAX('Table'[Sum of customers per date])
return
CALCULATE(MAX('Table'[ Date]),FILTER(ALLEXCEPT('Table','Table'[ Date]),'Table'[Sum of customers per date]=_maxCount))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @manojsv19 ,
Figured out the syntax of a the virtual table so that you don't need to create a calculated column. This replaces the last measure.
Date with highest count of customers 2 =
VAR _newTable =
ADDCOLUMNS (
'Table',
"Sum of customers per d", CALCULATE (
SUM ( 'Table'[Customers Count] ),
ALLEXCEPT ( 'Table', 'Table'[ Date] )
)
)
VAR _newTableMax =
MAXX ( _newTable, [Sum of customers per d] )
var _calc = CALCULATE(MAX('Table'[ Date]),FILTER(_newTable,MAX([Sum of customers per d] )= _newTableMax))
RETURN
_calc
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
15 | |
10 | |
8 | |
8 | |
7 |