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
hi_world
Helper III
Helper III

special filter display all rows when at least one value is true in the filter

Hello, 

 

I need to display all rows when at least one value is true in the filter.

I made an example case of what I want to do (the need is something else of course, here it's just to chematize)

I cannot modify the power query and I cannot modify the data model.
So just with DAX or a special visual.

 

 

hi_world_0-1679549986137.png

 

Best regards

1 ACCEPTED SOLUTION

Hi,

I have solved this with a measure and a filter.  Download the solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

24 REPLIES 24
Ashish_Mathur
Super User
Super User

Hi,

This isn't exactly what you want but gets the result you want.  Write this measure

Measure 1 = CONCATENATEX(CALCULATETABLE(VALUES(Data[Country]),ALL(Data[Country])),Data[Country],", ")

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur 
Do you have an idea to display this : 

hi_world_0-1681459424877.png

with a third column as you calculated it in your measure :

Measure 1 = CONCATENATEX(CALCULATETABLE(VALUES(Data[Country]),ALL(Data[Country])),Data[Country],", ")


repeat for all rows of asia : Turky, China, Japan

for Europ :  Turky, Germany, Swiss

If it need to create another topic i will create it. 

Thank you so much for your help. 

Best regards

 

Hi,

I am not sure but i can try.  Share the download link of the PBI file.  Would you be OK with a calculated column/Query Editor solution or do you only want a measure solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur 
Thank you to take time to help me. i really appreciate it. 

Sorry but i dont have the permission to publish .pbix here ( i dont know why, maybe i am new user) i tried to put in in dropbox but my company block thoses website.
So i can share with you the tables : 

Continent :
 

id_continentcontinentid_country
1europe1
1europe2
1europe3
2asia1
2asia4
2asia5
3Africa6
3Africa7
3Africa8


country : 

id_countrycountry
1Turkey
2Germany
3Swiss
4China
5Japan
6south africa
7Ghana
8Egypt

 

City:

id_countryid_cityCity
11Istanbul
12Ankara
23Berlin

 

hi_world_0-1681551215711.png

 

creating a measure would be better if you can do it.
if this is impossible for you then I also take a calculated column.

our dataset sharing policy for end users is to push the creation of only measurements to the maximum and to avoid making calculated columns.

Thank you so much

Hi,

From these 3 tables, how can i arrive at the table image that you shared in your initial post where you had two rows of Europe and turkey.  Please tell me which fields should i drag from which tables to arrive at that table image.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur 
Thank you for trying help me again

Column Continent From Continent TableColumn Country from Country tableMeasure
europeTurkeyTurkey, Germany, Swisss
europeGermanyTurkey, Germany, Swisss
europeSwissTurkey, Germany, Swisss
asiaTurkeyTurkey, China, Japan
asiaChinaTurkey, China, Japan
asiaJapanTurkey, China, Japan


Best regards

Hi,

I can solve this in the Query Editor.  This code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUotLcovSAUyDJViddCEjDCFjMFCRkBWYnFmIlwbkoAJuoApWMAYyHJMK8pMBgmZYQqZYwpZKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id_continent = _t, continent = _t, id_country = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"id_country"}, Country, {"id_country"}, "Country", JoinKind.LeftOuter),
    #"Expanded Country" = Table.ExpandTableColumn(#"Merged Queries", "Country", {"country"}, {"Country"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Country",{"id_country"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"id_continent"}, {{"Continents", each _, type table [id_continent=nullable text, continent=nullable text, Country=nullable text]}, {"Countris", each Text.Combine([Country],", "), type nullable text}}),
    #"Expanded Continents" = Table.ExpandTableColumn(#"Grouped Rows", "Continents", {"continent"}, {"continent"})
in
    #"Expanded Continents"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

thank you so much for your reponse. 
i understand that it is really hard to do it in DAX. r
Have a nice day ! 

Hi,

It is fairly easy to do it in DAX if you remove the Country column from the visual.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you @Ashish_Mathur 
The end user need to have coutry in the visual. unfortunately, this is a user requirement.
So it is possible to do it either in Power query or in the database directly.
I will communicate this way to my users as long as I have no other alternatives.
Best regards, 

I have already shared the Power Query solution with you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

we are in directquery mode and we try to limit the modification in powerquery. We are recommended to do DAX measurements.

Hi,

I have solved this with a measure and a filter.  Download the solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much !! pretty nice solution 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you @Ashish_Mathur 
how can i select all row contains turkey ?.
When i select turky in slicer in my table i will see :

hi_world_1-1680759794840.png

 

 

hi_world_0-1680759680433.png

 

and if i select turkey and Egypt in my slicer i will see all rows 

hi_world_2-1680759901853.png


Thank you so much

 

I shared my solution with you on march 25.  That is the best i can do.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

It does not completely meet my needs but thank you very much for your help.

lbendlin
Super User
Super User

use a disconnected table for the country slicer.  Create a measure that "filters up"  from the selected country to the continent and back down to the list of countries. Use that measure as a visual filter.

Hello @lbendlin 
I'm stuck and I can't find a solution, if you can give me a little more details please? thanks

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.