Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vannipart
Frequent Visitor

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

 



1 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 

View solution in original post

8 REPLIES 8
vannipart
Frequent Visitor


Here are three situations situations, that i have.all.png

Screen Shot 2017-08-15 at 15.54.44 (2).pngScreen Shot 2017-08-15 at 17.21.48.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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])))

4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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])]})

5.PNG

 

 

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

6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.