• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Bear

Member
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.
 

Attachments

Chihiro

Excel Ninja
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....

Code:
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.
 

Attachments

Bear

Member
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....

Code:
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

Well-Known Member
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"))
 
Top