Forum Discussion

hprose's avatar
hprose
Icon for Helper I rankHelper I
2 years ago
Solved

Remove blank from field parameter and sort by descending

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, ...
Need Help
hprose's avatar
hprose
Icon for Helper I rankHelper I
2 years ago

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's avatar
OguzMavice
Icon for Resolver II rankResolver II
2 years ago

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's avatar
    hprose
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      OguzMavice
      Icon for Resolver II rankResolver II
      2 years ago

      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's avatar
        hprose
        Icon for Helper I rankHelper I
        2 years ago

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