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 

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