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
Roy_B
Helper I
Helper I

how to get the maximum value within a selection and use that value to filter a computed column

I have a view that contains info similar to the table below and to display this data I’m using a matrix.

The columns that are NOT marked with YTD can be summarized normally using the sum function

Columns that Are YTD can either be one of two things based on what date the user selected. if the user selects no dates then the maximum date is used and the totals within the matrix would be  6, 30, 10 for plan YTD, actual YTD and last week YTD. If the user selects 7/25/2020 the total will be 75, 100 30. 

but if the user selects BOTH 7/25 and 8/8 then only the result for the date 8/8 should show 

week#

Product

Plan

Actual

last Week

date

Plan YTD

Actual YTD

last Week YTD

30

A

15

5

1

7/25/2020

20

50

30

30

B

0

1

1

7/25/2020

25

50

0

30

C

15

5

0

7/25/2020

30

0

0

31

A

0

0

2

8/1/2020

10

25

20

31

B

0

0

2

8/1/2020

11

25

0

31

C

10

5

0

8/1/2020

12

0

0

32

A

9

3

0

8/8/2020

1

0

10

32

B

0

3

3

8/8/2020

2

20

0

32

C

0

0

3

8/8/2020

3

10

0

 

I’m using this formula in one of the computed columns

 

 
calc_ACTUAL_YTD = IF(Query1[week#] = [Measure], Query1[ActualYTD], 0)

 

 

for the Measure I have tried these but none seem to work

 

Measure = CALCULATE( MAX(Query1[week#]), ALLSELECTED(Query1))
Measure = WEEKNUM(LASTDATE(ALLSELECTED(Query1[Date])))
Measure = MAX(Query1[week#])

 

 

But I either get the maximum date value or I get the week value that I need but all values in the [calc_ACTUAL_YTD] column is 0 except for the max date or it still sums everything up

 

 

 

 

1 ACCEPTED SOLUTION

Hi @Roy_B ,

 

Like this?

 

--Before filtering the [date] column

v-lionel-msft_0-1598950057235.png

--After filtering the [date] column

 

v-lionel-msft_0-1598950168404.png

__A_YTD = 
VAR x = MAXX( FILTER( ALL(Sheet3), Sheet3[Product] = MAX(Sheet3[Product]) ), [date] )
VAR y = 
CALCULATE(
     SUM([Actual YTD]),
     FILTER(
         ALL(Sheet3),
         Sheet3[date] = x
     )
)
RETURN
IF(
    ISFILTERED(Sheet3[date]),
    CALCULATE(
        SUM(Sheet3[Actual YTD]),
        FILTER(
            Sheet3,
            Sheet3[date] = x
        )
    ),
    y
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
pranit828
Community Champion
Community Champion

HI @Roy_B 

try something like

CALCULATE (
    COUNTROWS('Raw Data'),
    FILTER ( ALLEXCEPT('Raw Data','Raw Data'[Decision]),'Raw Data'[Decision]="Award")
)

or

VAR SearchValue = <Search_Value>
RETURN
    CALCULATE (
        SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ),
        FILTER (
            ALLNOBLANKROW ( <Search_ColumnName> ),
            <Search_ColumnName> == SearchValue     -- The == operator distinguishes between blank and 0/empty string
        ),
        ALL ( <table_of_Result_ColumnName> )       -- If Result_ColumnName is t, this is ALL ( t )
    )

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Filter value is not known on design time.

What ever fomula I use it must only get the maximum value from the seelcted filtersBI_Problem.jpg

Hi @Roy_B ,

 

Like this?

 

--Before filtering the [date] column

v-lionel-msft_0-1598950057235.png

--After filtering the [date] column

 

v-lionel-msft_0-1598950168404.png

__A_YTD = 
VAR x = MAXX( FILTER( ALL(Sheet3), Sheet3[Product] = MAX(Sheet3[Product]) ), [date] )
VAR y = 
CALCULATE(
     SUM([Actual YTD]),
     FILTER(
         ALL(Sheet3),
         Sheet3[date] = x
     )
)
RETURN
IF(
    ISFILTERED(Sheet3[date]),
    CALCULATE(
        SUM(Sheet3[Actual YTD]),
        FILTER(
            Sheet3,
            Sheet3[date] = x
        )
    ),
    y
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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.