Group then show items only <> $0

ExcelQuestion

New member
Joined
May 27, 2018
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
Dear Experts,

Using Power Query, I was able to group item based on:
1) [Date] <= "Cutoff Date" parameter input, AND;
2) [Match ID] <> total $0

Group.jpg

Trouble is...the user experience isn't intuitive for the common office environment. The user has to enter the "Cutoff Date", then Refresh.
Huge risk...when the user doesn't Refresh but just prints the un-freshed report.
Also, my actual Source Table is a larger dataset utilizing relationships...prefer not to Merge existing data into the Power Query just for this one report.
Lastly, all my existing reports are DAX pivot tables...trying keep the same look and feel for the audience.

How do you create the Power Query method using DAX pivot table? Definitely need to show the Cutoff Date as part of the pivot table.

Thanks in advance,
Ricky
 

Attachments

  • 2019-05-11 Grouping.xlsx
    21.4 KB · Views: 11

gue

New member
Joined
Nov 28, 2018
Messages
20
Reaction score
0
Points
0
Excel Version(s)
Office Professional Plus 2016
for your first problem you could use the worksheet change event so that the user need not refresh
Code:
Private Sub Worksheet_Change(ByVal Target As Range)  
  
  If Target.Address = Me.Range("CutoffDate").Address Then ThisWorkbook.RefreshAll

End Sub

of course you have to change the xlsx file to a macro enabled xlsm file
 

ExcelQuestion

New member
Joined
May 27, 2018
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
Thank-you gue!

Could this problem be solved using Power Pivot instead?

Thanks again,
Ricky
 

gue

New member
Joined
Nov 28, 2018
Messages
20
Reaction score
0
Points
0
Excel Version(s)
Office Professional Plus 2016
I dont think so.
your data is already in a pivoted form. So you have to unpivot it first which means that you have to use power query and therefore a trigger event.
If you want to use a slicer the problem is that the slicer uses only values out of your data and if you need a not available date as cutoff date you have either link a special table including all days or may be only last days of months but as I know you have again to trigger a refresh somehow.
 

ExcelQuestion

New member
Joined
May 27, 2018
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hi gue,
You're right about the slicers.

Suppose entering input cutoff date cell (as a separate table) then Refresh is acceptable, is there a DAX measure that show only the "Match ID" <>0?

Thanks again,
Ricky
 
Top