Conditional filtering based on specific row value, with several dashboards

[vannipart]

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 "all" included into their country tables. If I select 'All' then other dashboards will be empty and the only one that uses "All" shows data. IF no country is selected then other visuals will show what they should but the one that needs all shows wrong data. I don't want to delete the interaction between the visuals, I need some type of a conditional filtering, but haven’t figured it out. 

All tables are connected to country table, which is an extra table and includes "All".

Would really appreciate any type of help.

What i need is that when nothing is selected, then special visual which requiers "all" is selected and when all is selected other visuals will act like nothing is selected.

[vannipart]

Here are three situations situations, that i have.

[Anonymous]

Hi @vannipart,

Can you please share a sample pbix file to test? Based on your screenshot, it is hard to reproduce your issue.

Regards,

Xiaoxin Sheng

[vannipart]

Hello, 

I made an example table. So the right combinationm, when slicer nothing is selected would be age=39 and unti 19872873 and if all is selected then age should be 39 and 19872873. Of course by chaning country the numbers will go in an correct way. 


https://pictureskk.blob.core.windows.net/pic2/EXAMPLEALL.pbix

Best of luck, 
Kaarel

[Anonymous]

Hi @vannipart,

For your scenario, the simple way is use current table to create new table with summary row, then build relationship to country table and use new table as the source of table visual.

Table = UNION('NO ALL DATA',ROW("country","ALL","unit",SUM('NO ALL DATA'[unit])))

Regards,

Xiaoxin Sheng

[vannipart]

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]

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 · Accepted solution]

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 

[vanessafvg]

@vannipart  be really helpful here if you could provide some screen shots.  visuals give better understanding