Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Hope anyone can help me with this.
I am trying to get Total number of O&A Display for only the highest callfilevisit ID for Each outlet.
i.e outlet "1000245" will only need to show a value of 2 from callfilevisit ID "28232875" and so on, then sum all the values.
I have tried Max function but it only brings max callfilevisit ID for all outlets.
Any advice or help would be greatly appreciated.
Solved! Go to Solution.
try this code
Measure = VAR OutletsVisits = GROUPBY ( 'Table', 'Table'[OutletId], "Callfilevisit ID", MAXX ( CURRENTGROUP (), [Callfilevisit ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[Total O&A Displays] ), TREATAS ( OutletsVisits, 'Table'[OutletId], 'Table'[Callfilevisit ID] ) )
it gets the max callfilevisit per outlet and then calculates the sum of displays for these entries
try this code
Measure = VAR OutletsVisits = GROUPBY ( 'Table', 'Table'[OutletId], "Callfilevisit ID", MAXX ( CURRENTGROUP (), [Callfilevisit ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[Total O&A Displays] ), TREATAS ( OutletsVisits, 'Table'[OutletId], 'Table'[Callfilevisit ID] ) )
it gets the max callfilevisit per outlet and then calculates the sum of displays for these entries
Wow. This works perfectly. Thanks.
Could you please tell me why i need to use treatas function and what it does?
Cheers
it passes the requested list of outlets & visits as a filter on the relevant fields
this article describes it in more detail
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |