Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dankang
Regular Visitor

simple dax question

Max.png

 

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.

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

3 REPLIES 3
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Wow. This works perfectly. Thanks.

 

Could you please tell me why i need to use treatas function and what it does?

 

Cheers

Stachu
Community Champion
Community Champion

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/



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.