Dax Measure not showing all result

[sdm2211]

Dear Team,

I am fetching highest score by batsman scored in an inning by below DAX measure.

M Highest Score =

VAR Temp =

   FILTER(

         SUMMARIZE(

            Deliveries,

            Deliveries[Match_ID],

            Deliveries[Innings],

            "run", SUM(Deliveries[Batsman_Runs])

         ),

         [run]>=100

   )

   RETURN

   MAXX(Temp, [run])

I know I am using MAXX fuction hence getting only highest score for e.g. Batsman A scored 102 & 110 in different inning, then the above DAX measure will only show 110. 

Is there any way to give the result of all records instead of MAXX.

https://drive.google.com/file/d/1MQp_V0FxjiW1VIyB6H7NCnC5rCzuAdaj/view?usp=sharing

https://docs.google.com/spreadsheets/d/1Y7yEGlMZDVcjWtDIeppiJx6V-_-h-OqN/edit?usp=sharing&ouid=11539...

[johnt75 · Accepted solution]

Your current measure will work, you just need to add more data into the table, to uniquely identify the match and innings.

Set the 'Deliveries'[Innings] column to "do not summarize" and then include that and the match number, or some other column to uniquely identify the match, in the table visual. If you don't want to display them visually then you could either set the column width to 0 or use the visual calculation functionality to hide them.

[v-xinc-msft]

Hi @sdm2211 ,

Did the replies above offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Best regards,

Lucy Chen

[thefarside]

@sdm2211  I'm not entirely clear what results you are hoping to calculate, but I hope my answer helps!
If you're visual (table) has the batsman and inning fields, and your [M Highest Score] measure as columns, you just need to change the MAXX function to SUMX. You'll get something like this:

Alternatively, if you're looking to return batsman, innings, and scores all in one measure (not recommended) you can do this:

M All High Scores = 
VAR Temp =
    FILTER(
        SUMMARIZE(
            Deliveries
            , Deliveries[Match_ID]
            , Deliveries[Innings]
            , Deliveries[Batsman]
            , "run", SUM(Deliveries[Batsman_Runs])
        ),
        [run] >= 100
    )
RETURN
    CONCATENATEX(
        Temp
        , Deliveries[Batsman] & " - Innings " & Deliveries[Innings] & ": " & [run]
        , UNICHAR(10)
        , Deliveries[Batsman] , ASC
        , Deliveries[Innings], ASC
    )

Results will look something like this:

I hope that's helpful!

[sdm2211]

hello @thefarside thanks for this code. However I am not looking for this kind of result. 

I am looking for the result which is attached here https://drive.google.com/file/d/1GWrjEMoFenMjhdTqYcXCFhDM7EuTEhXG/view?usp=sharing

you can see KS Williamson is appeaing two times, one at 8th place with score 251 and another at 12th place with score 238. I want DAX will give me exact same result and should not return the result where only maximum score i.e. 251 will show. Currently I am using MAXX that is why showing only 251.

Could you please help me here.

https://drive.google.com/file/d/1MQp_V0FxjiW1VIyB6H7NCnC5rCzuAdaj/view?usp=sharing

https://docs.google.com/spreadsheets/d/1Y7yEGlMZDVcjWtDIeppiJx6V-_-h-OqN/edit?usp=sharing&ouid=11539...

https://drive.google.com/file/d/1GWrjEMoFenMjhdTqYcXCFhDM7EuTEhXG/view?usp=sharing

[lbendlin]

Is there any way to give the result of all records instead of MAXX.

Measures must return a scalar (a single value).  You cannot return "all records".

what would be the expected outcome based on your sample data?