Want to show "dates" in the filter of pivot table instead of multiple items

[Bear]

Hi

I put date info into the filter of the pivot table. The filter showed multiple items when users selected more than a date.

I found that I can use "textjoin" function together with another filter(actually it is another pivot table by dragging the date info to row). However, I face another problem. the blank cells showing "Jan-00".

My excel file is attached. In a nutshell, I want my result in this case showing "Jan-20 & Feb-20 & Mar-20"

Hope anyone can help or provide other methods to solve the filter problem.

 

[Chihiro]

Since you posted in Power Query/Power Pivot forum. I assume that you are using OLAP based pivot table.

Data model is somewhat limited in formatting that can be applied. So in this case I'd recommend using PQ to add display value as well.
While its possible to do in DAX measure, it's more performant to do so in PQ.

Then you need to add DAX measure to concatenate values selected.

After that, you will need to create copy of the Pivot table and link it via slicer.

Exact DAX measure construct will depend on your data model structure and relationship. But general construct is like below....

Selected :=
VAR _ConCat =
    CALCULATE (
        CONCATENATEX ( VALUES ( Table1[Display] ), Table1[Display], " & " )
    )
VAR _IsFiltered =
    ISFILTERED ( Table1[Date] )
RETURN
    IF ( _IsFiltered, _ConCat, "No Filter Applied" )

See sample attached.

Pivot has the filter field and 2 pivots. Setup has Slicer to link two pivots together. In practice, you can hide the Setup sheet.

 

[Bear]

Since you posted in Power Query/Power Pivot forum. I assume that you are using OLAP based pivot table.

Data model is somewhat limited in formatting that can be applied. So in this case I'd recommend using PQ to add display value as well.
While its possible to do in DAX measure, it's more performant to do so in PQ.

Then you need to add DAX measure to concatenate values selected.

After that, you will need to create copy of the Pivot table and link it via slicer.

Exact DAX measure construct will depend on your data model structure and relationship. But general construct is like below....

Selected :=
VAR _ConCat =
    CALCULATE (
        CONCATENATEX ( VALUES ( Table1[Display] ), Table1[Display], " & " )
    )
VAR _IsFiltered =
    ISFILTERED ( Table1[Date] )
RETURN
    IF ( _IsFiltered, _ConCat, "No Filter Applied" )

See sample attached.

Pivot has the filter field and 2 pivots. Setup has Slicer to link two pivots together. In practice, you can hide the Setup sheet.

Thanks Chihiro
Really appreciate your help.

 

[p45cal]

A bit late, but you could try a tweak to the formula in cell G1:
=TEXTJOIN(" & ",TRUE,TEXT(FILTER(N1:N8,N1:N8>0),"mmm-yy"))