cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## Trying to get the largest values in a summary of a detail table.

Hi, I'm trying to pull out the Amount and the largest change in % between two dates from a table that contains Directory, Detail and date and place it in a card, based on the Directory chosen by the user.
I keep running into challenges with the code. Can you help?

For example, with this subset of the data, I would want to get the biggest amount of data on what day, and the largest % between two, as 2 seperate measures, and place each one in a separate card.

Here's my example,
User chooses Dir1 from a dropdown
the data on the left is the detail and the summary on the right is created from it.

So what I'm trying to do is to capture a few measures.
* The largest summary and date, created from the detail
* The largest % and the date of that % created from the detail

I keep trying to build filters and each one fails.
Can I get some support on creating the filters that would be needed for this?

Thanks, Stesven

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @MRUry7 ,

Please try to create measure with below dax formula:

``````Table 2 =
SUMMARIZE (
'Table',
'Table'[Date],
'Table'[Directory],
"Sumary", SUM ( 'Table'[Detail] )
)
``````
``````% =
VAR dir =
SELECTEDVALUE ( 'Table 2'[Directory] )
VAR cur_date =
SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
FILTER ( ALL ( 'Table 2' ), [Directory] = dir && [Date] < cur_date )
VAR pre_date =
MAXX ( tmp, [Date] )
VAR _a =
CALCULATE ( SUM ( 'Table 2'[Sumary] ), FILTER ( tmp, [Date] = pre_date ) )
VAR _b =
CALCULATE (
SUM ( 'Table 2'[Sumary] ),
FILTER ( ALL ( 'Table 2' ), [Directory] = dir && [Date] = cur_date )
)
RETURN
DIVIDE ( _b - _a, _a, 0 )
``````
``````Max % With Date =
VAR tmp =
FILTER (
ALL ( 'Table 2' ),
[Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
)
VAR _val =
MAXX ( tmp, [%] )
VAR _date =
CALCULATE (
MAX ( 'Table 2'[Date] ),
FILTER (
ALL ( 'Table 2' ),
[Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
&& [%] = _val
)
)
RETURN
_date & ": "
& FORMAT ( _val, "percent" )
``````
``````Max Sumary With Date =
VAR tmp =
FILTER (
ALL ( 'Table 2' ),
[Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
)
VAR _val =
MAXX ( tmp, [Sumary] )
VAR _date =
CALCULATE (
MAX ( 'Table 2'[Date] ),
FILTER (
ALL ( 'Table 2' ),
[Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
&& [Sumary] = _val
)
)
RETURN
_date & ": " & _val
``````

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver II

This was excellent! It not only literally gave me the solution, it allowed me to store this as a template going forward for future endeavors like this.

I really appreciate your help and it gave me the answer I was looking for.  IT's just too bad that there isn't a VBA like code that code have been used.  In Excel this would have been so simple to do.

Thanks!

2 REPLIES 2
Anonymous
Not applicable

Hi @MRUry7 ,

Please try to create measure with below dax formula:

``````Table 2 =
SUMMARIZE (
'Table',
'Table'[Date],
'Table'[Directory],
"Sumary", SUM ( 'Table'[Detail] )
)
``````
``````% =
VAR dir =
SELECTEDVALUE ( 'Table 2'[Directory] )
VAR cur_date =
SELECTEDVALUE ( 'Table 2'[Date] )
VAR tmp =
FILTER ( ALL ( 'Table 2' ), [Directory] = dir && [Date] < cur_date )
VAR pre_date =
MAXX ( tmp, [Date] )
VAR _a =
CALCULATE ( SUM ( 'Table 2'[Sumary] ), FILTER ( tmp, [Date] = pre_date ) )
VAR _b =
CALCULATE (
SUM ( 'Table 2'[Sumary] ),
FILTER ( ALL ( 'Table 2' ), [Directory] = dir && [Date] = cur_date )
)
RETURN
DIVIDE ( _b - _a, _a, 0 )
``````
``````Max % With Date =
VAR tmp =
FILTER (
ALL ( 'Table 2' ),
[Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
)
VAR _val =
MAXX ( tmp, [%] )
VAR _date =
CALCULATE (
MAX ( 'Table 2'[Date] ),
FILTER (
ALL ( 'Table 2' ),
[Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
&& [%] = _val
)
)
RETURN
_date & ": "
& FORMAT ( _val, "percent" )
``````
``````Max Sumary With Date =
VAR tmp =
FILTER (
ALL ( 'Table 2' ),
[Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
)
VAR _val =
MAXX ( tmp, [Sumary] )
VAR _date =
CALCULATE (
MAX ( 'Table 2'[Date] ),
FILTER (
ALL ( 'Table 2' ),
[Directory] = SELECTEDVALUE ( 'Table 2'[Directory] )
&& [Sumary] = _val
)
)
RETURN
_date & ": " & _val
``````

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver II

This was excellent! It not only literally gave me the solution, it allowed me to store this as a template going forward for future endeavors like this.

I really appreciate your help and it gave me the answer I was looking for.  IT's just too bad that there isn't a VBA like code that code have been used.  In Excel this would have been so simple to do.

Thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors