Slicer to select "group by" field in table

[MarkEvansHW]

I have table like this:

quarter	month	week	date	sales
2020-Q3	2020-09	2020-35	2020-09-01	$14,43.85
2020-Q3	2020-08	2020-35	2020-08-31	$32,432.32
2020-Q3	2020-08	2020-35	2020-08-30	$986.45
2020-Q3	2020-08	2020-34	2020-08-29	$11,344.00
...	...	...	...	...

Here's an image of above table as it is displaying incorrectly in my browser:

I have a slicer that allows the user to pick between "quarter", "month", week", and "date".

I'd like the resulting table to include a field (with no summary function) that groups by the appropriate period field based on the selection. In the example below, the user has selected "quarter":

Period (dynamic)	sales (sum)
2020-Q3	$983,324.23
2020-Q2	...etc

Here's an image of above table:

Perhaps further complicating this is that the source tables are "Direct Query". Thanks for your suggestions.

[v-stephen-msft]

Hi @MarkEvansHW ,

Create a Category table for slicer. There is no relationship between two tables.

Then try this measure.

Period = 
SWITCH (
    SELECTEDVALUE ( Category[Category] ),
    "date", MAX ( 'Table'[date] ),
    "month", MAX ( 'Table'[month] ),
    "quarter", MAX ( 'Table'[quarter] ),
    "week", MAX ( 'Table'[week] )
)

You can check more details from here.

Best Regards,

Stephen Tao

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

[MarkEvansHW]

I see - it is because in your fact table, the sales column is a text field and in the table visual you're showing it is "not summarized". I need for this to be a numeric field that sums in the solution. Thanks for the suggestion.

[MarkEvansHW]

I'm trying to get it to summarize by the different levels. In your demo, the rows are not totalling for quarter, month, etc. There should be one row for the quarter, two rows for the months and so on.

I'm not actually sure how you're getting more than one row, tbh. When I implement similar solution, I get the MAX date for the given period type (day, week, month, etc) and it returns one row with that max date and the total sum of the other field.

[Greg_Deckler]

@MarkEvansHW In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[MarkEvansHW]

Yes, I have a measure that works that way using the disconnected table. However, I don't see any way to have a non-measure (not use SUM, MIN, MAX, etc) in a field in the resulting table. Thanks.