Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Best regards
Solved! Go to Solution.
Hi,
I have solved this with a measure and a filter. Download the solution file from here.
Hope this helps.
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.
Hello @Ashish_Mathur
Do you have an idea to display this :
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?
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_continent | continent | id_country |
1 | europe | 1 |
1 | europe | 2 |
1 | europe | 3 |
2 | asia | 1 |
2 | asia | 4 |
2 | asia | 5 |
3 | Africa | 6 |
3 | Africa | 7 |
3 | Africa | 8 |
country :
id_country | country |
1 | Turkey |
2 | Germany |
3 | Swiss |
4 | China |
5 | Japan |
6 | south africa |
7 | Ghana |
8 | Egypt |
City:
id_country | id_city | City |
1 | 1 | Istanbul |
1 | 2 | Ankara |
2 | 3 | Berlin |
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.
Hello @Ashish_Mathur
Thank you for trying help me again
Column Continent From Continent Table | Column Country from Country table | Measure |
europe | Turkey | Turkey, Germany, Swisss |
europe | Germany | Turkey, Germany, Swisss |
europe | Swiss | Turkey, Germany, Swisss |
asia | Turkey | Turkey, China, Japan |
asia | China | Turkey, China, Japan |
asia | Japan | Turkey, 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.
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.
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.
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.
Thank you so much !! pretty nice solution
You are welcome.
Thank you @Ashish_Mathur
how can i select all row contains turkey ?.
When i select turky in slicer in my table i will see :
and if i select turkey and Egypt in my slicer i will see all rows
Thank you so much
I shared my solution with you on march 25. That is the best i can do.
It does not completely meet my needs but thank you very much for your help.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |