Forum Discussion

vannipart's avatar
vannipart
Frequent Visitor
9 years ago
Solved

Conditional filtering based on specific row value, with several dashboards

Hello I have a list of countries and then there is a special "all" also included, which is made for one specific visual, to show the overall value.  Other visuals in the dashboard don't have "a...
Need Help
  • vannipart's avatar
    vannipart
    9 years ago

    Hello Xiaoxin, 

     

     

    Thank you for you effort!
    I found a way to work around it, it is not perfect but looks like that. 

     

    Measure = if(iscrossfiltered([averageAge]),FIRSTNONBLANK(age[averageAge],

    [averageAge]),CALCULATE(FIRSTNONBLANK(age[averageAge],[averageAge]), FILTER(age,[country] = "All")))

     

     

    I hidde "all" from slicer view, so when nothing is selected then the averageage is 39 and the unit is what it should be and if a country is selected then numbers will be correct.

    Thank you again for your effort!


    Kaarel 

vannipart's avatar
vannipart
Frequent Visitor
9 years ago

Thank you Xiaoxin!

Creating special table for "all" is one option, but it isn't solving my problem if nothing is selected. I have several different visuals which all would require this and automatically being updated all the time. 

 

 

 

Thank you very much.

 

Kaarel

Anonymous's avatar
Anonymous
Not applicable
9 years ago

HI vannipart,

 

>>Creating special table for "all" is one option, but it isn't solving my problem if nothing is selected. 

Measure can do some specific operation to deal with 'all' item selected, but it can't as the group column of visual. For your scenario, you can try to add total row in power query.

 

Query:

 

Custom1 = Table.InsertRows(#"Previous Step",Table.RowCount(#"Previous Step"),{[ColumnName1="ALL",ColumnName2=List.Sum(#"Previous Step"[ColumnName2])]})

 

Sample:

 

Custom1 = Table.InsertRows(#"Removed Columns",Table.RowCount(#"Removed Columns"),{[country="ALL",unit=List.Sum(#"Removed Columns"[unit])]})

 

 

For query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZFNcoMwDIXv4nUWRjLGLB2wW1pCmBhCJgz3v0Yl2emk3QB+3/PTD/uu4jCpk3LQWDQtfaU5dIMf1XHaVR9WUhA1GrTwH14vAonRuxMt3jxrJmupj0nk7jPQETTrzghJvZCP840JQvaLtiYOqa0huYLsFpA2iUEEVvsUg8hfM09QgSO/0ezvMpg4p2lrMAiu5iZLhXMYc1V6hpIyXbkTg+QGy4sIfQb99F2qItRO2nnlPH18y0nZH9LCQbVBbFjfSgG/LjmH+wdGrxvj+uCt5Rz/qts9edrKNKTrPySkmf0t35AgIj4PJvu3TvNGwb6jOfB8lW4JuJK2ZTQsXn4PMKoKyyjdebc1VoRKJdEv4fE7uQ9RHccP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [country = _t, unit = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"country", type text}, {"unit", Int64.Type}, {"Column1", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
    Custom1 = Table.InsertRows(#"Removed Columns",Table.RowCount(#"Removed Columns"),{[country="ALL",unit=List.Sum(#"Removed Columns"[unit])]})
in
    Custom1

 

Regards,

Xiaoxin Sheng

  • vannipart's avatar
    vannipart
    Frequent Visitor
    9 years ago

    Hello Xiaoxin, 

     

     

    Thank you for you effort!
    I found a way to work around it, it is not perfect but looks like that. 

     

    Measure = if(iscrossfiltered([averageAge]),FIRSTNONBLANK(age[averageAge],

    [averageAge]),CALCULATE(FIRSTNONBLANK(age[averageAge],[averageAge]), FILTER(age,[country] = "All")))

     

     

    I hidde "all" from slicer view, so when nothing is selected then the averageage is 39 and the unit is what it should be and if a country is selected then numbers will be correct.

    Thank you again for your effort!


    Kaarel