Remove blank from field parameter and sort by descending

[hprose]

Hi everyone,

Please see sample data in attached link.

I need to show the sum of 'Value' in a matrix for different time period combinations. I've created a field parameter with fields YTD, week, month and quarter. One parameter is acting as the slicer to select the column I need. The duplicated slicer shows selected values. The following is the result. 

1) Is there a way to get rid of the blank in the second parameter slicer? 2) Can the values in the second slicer be sorted in descending order so only the latest week/month/quarter is selected when we switch between the first slicer options? When I sort by descending, it doesn't persist when switching between options. Note that both slicers need to be single select.

The calculation for Value1 is simple - 

Value1 = CALCULATE(SUM('Fact'[Value]))

PBIX file - removed

Thank you

[hprose · Accepted solution]

For anyone looking for solution, I unfortunately had to do it the hard way to make the filters user-friendly. That involved creating bookmarks and buttons for various combinations as mentioned in this post - https://community.fabric.microsoft.com/t5/Desktop/Displaying-single-graph-based-off-2-sets-of-bookma...

[OguzMavice]

Hi @hprose , how did you create field parameter? If your field parameter depends on "Type" column; blank column will be disappeared.  you can use "selectedvalue" and "switch" function to reach your goal also. 

[hprose]

Hi @OguzMavice , my field parameter is created from the 4 columns

Type = {

    ("YtoD", NAMEOF('Fact'[YtoD]), 0),

    ("Week", NAMEOF('Fact'[Week]), 1),

    ("Month", NAMEOF('Fact'[Month]), 2),

    ("Quarter", NAMEOF('Fact'[Quarter]), 3)

}

I cannot use the 'Type' column for the field parameter because I need the values in the ytd, week, month and year columns in the duplicated parameter slicer. 

Please see pbix with the sample data here: removed

[OguzMavice]

Hi  @hprose ,   

I created one calculated column and one measure instead of using field parameter. Let me show the results;

In order to get this result, first I created a measure;

Solution =

VAR MySelection =

    SELECTEDVALUE ( 'Fact'[Type] )

RETURN

    SWITCH (

        TRUE (),

        MySelection = "W", CALCULATE(SUM('Fact'[Value]),'Fact'[Type]="W"),

        MySelection = "Y", CALCULATE(SUM('Fact'[Value]),'Fact'[Type]="Y"),

        MySelection = "M", CALCULATE(SUM('Fact'[Value]),'Fact'[Type]="M"),

        MySelection = "Q",CALCULATE(SUM('Fact'[Value]),'Fact'[Type]="Q")

    )

then I created a  calculated column in the fact table;

 Content =

var week =CONVERT('Fact'[Week],STRING)

var year = CONVERT('Fact'[YtoD],STRING)

var q = CONVERT('Fact'[Month],STRING)

return TRIM(COMBINEVALUES(" ",week,year,q,'Fact'[Quarter])) 

I use two slicer; the first one I added "type column" ( you can give name as you wish for composition( Week instead of W, etc..); the second one I added "content" column.

[hprose]

Thank you for your response, I implemented your approach in my file. However, I notice that selecting a value in the second slicer removes options from the first slicer (which is expected).

The reason I was trying to use field parameters was so all the options would be visible to the viewer under 'Type' at all times and both slicers can be single select with the latest week/month/quarter selected. Would that be possible?

Example #1

Exmple #2

[OguzMavice]

Hi @hprose , if you change settings of interaction on page, you can handle with this problem. you should select content slicer and click edit interaction then off interaction icon on the type slicer.

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

[hprose]

Thanks again, Edit Interactions helps partially. However, the last filter selection persists when switching between different options in 'Type' and the matrix ends up being blank unless something is selected in 'Content'.

[OguzMavice]

I see @hprose , let me recommend extra solution with field parameters.

if I use this filter for slicer which has blank data , it will generate result you wish I guess.

[hprose]

Thank you @OguzMavice , my problem is 70% solved. However, it seems like power bi does not allow sorting in descending order so the earliest date is selected instead of latest by default. And only Type Q seems to not have anything selected by default for some reason and therefore leaves the matrix blank.