Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I am rather new with Power BI and came across a situation I don't know how to resolve.
I have 2 tables: one with users which contains the user ID and 4 criteria
User ID | Budget | Neighbourhoods | Number of rooms | Proximity to metro station |
1 | 10000 | A, B, C, D | 1, 2 | 5 minutes |
2 | 5000 | A, E, J, K | 1 | 15 minutes |
3 | 3000 | B, C, D, E | 3 | 10 minutes |
4 | 10000 | A, B, C, D, E | 3 | not important |
5 | 11000 | A, E | 3, 4 | not important |
6 | 7000 | A, B | 1, 2, 3, 4 | 10 minutes |
And one with apartments
Apartment ID | Price | Neighbourhood | Number of rooms | Proximity to metro station |
1 | 9750 | A | 2 | 5 minutes |
2 | 4000 | A | 4 | 10 minutes |
3 | 2550 | E | 3 | 15 minutes |
4 | 4550 | B | 3 | 10 minutes |
5 | 7550 | C | 1 | out of range |
6 | 6000 | A | 1 | 10 minutes |
For each table, I need to create the 6th column:
User ID | Budget | Neighbourhoods | Number of rooms | Proximity to metro station | Number of apartments according to criterias |
1 | 10000 | A, B, C, D | 1, 2 | 5 minutes | 1 (apt 1,6) |
2 | 5000 | A, E, J, K | 1 | 15 minutes | 0 |
3 | 3000 | B, C, D, E | 3 | 10 minutes | 0 |
4 | 10000 | A, B, C, D, E | 3 | not important | 2 (apt 3,4) |
5 | 11000 | A, E | 3, 4 | not important | 2 (apt 2,3) |
6 | 7000 | A, B | 1, 2, 3, 4 | 10 minutes | 3 (apt 2,4,6) |
Apartment ID | Price | Neighbourhood | Number of rooms | Proximity to metro station | Number of persons according to criterias |
1 | 9750 | A | 2 | 5 minutes | 1 (user 1) |
2 | 4000 | A | 4 | 10 minutes | 2 (users 5, 6) |
3 | 2550 | E | 3 | 15 minutes | 2 (users 4,5) |
4 | 4550 | B | 3 | 10 minutes | 2 (users 4,6) |
5 | 7550 | C | 1 | out of range | 0 |
6 | 6000 | A | 1 | 10 minutes | 1 (user 6) |
Some complexities:
Thank you for your help
Mihai
Solved! Go to Solution.
Can be done in DAX as well following a similar logic. Here is a possible solution in M, where ApartmentsT is the name of your apartments table. See it all at work in the attached file.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0AAIg7aij4KSj4Kyj4AIS1FEwAlKmCrmZeaUlqcVKsTrRSmARuGJXHQUvHQVvkGIQRlVqDBQyhiiFGgrUABIDW4ii1ASrGxDK8/JLFDJzC/KLShLzSsA6TEE6DBEOASnUUTDBqtYMKGqOMBzqNR0FqAZkt8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Budget = _t, Neighbourhoods = _t, #"Number of rooms" = _t, #"Proximity to metro station" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Budget", Int64.Type}, {"Neighbourhoods", type text}, {"Number of rooms", type text}, {"Proximity to metro station", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Numb apartments meeting criteria", each Table.RowCount(Table.SelectRows(ApartmentsT,
(inner)=> inner[Price]<=[Budget] and Text.Contains([Neighbourhoods], inner[Neighbourhood])
and Text.Contains([Number of rooms], Text.From(inner[Number of rooms]))
and (if [Proximity to metro station] = "not important" then true else if inner[Proximity to metro station]="out of range" then false
else if Number.FromText(Text.BeforeDelimiter(inner[Proximity to metro station]," minutes")) <= Number.FromText(Text.BeforeDelimiter([Proximity to metro station]," minutes")) then true else false))))
in
#"Added Custom"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Try this for the custom column
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.RowCount(Table.SelectRows(Apartamente,
(inner)=> inner[#"Pret cu TVA calculat"]<=[#"Buget maxim disponibil"] and
(if [#"Zone de interes:"]=null then true else if inner[#"Zona"]=null then false else Text.Contains([#"Zone de interes:"], inner[#"Zona"]))
and Text.Contains([#"Cauti un apartament cu ..... camere"], Text.From(inner[#"Numar camere (numar)"]))
and (if List.Contains({null,"nu este important"},[#"Distanta metrou (bifeaza optiunea maxim acceptabila): "]) then true else if inner[#"Distanta fata de metrou"]="Peste 20 minute" then false
else if inner[#"Distanta fata de metrou"] = "peste 20 minute" then false else
if Number.FromText(List.Max(Text.Split(Text.BeforeDelimiter(inner[#"Distanta fata de metrou"]," minute"),"-"))) <= Number.FromText(List.Max(Text.Split(Text.BeforeDelimiter([#"Distanta metrou (bifeaza optiunea maxim acceptabila): "]," minute"),"-"))) then true else false
))))
You might have to tweak a bit to adapt it to your requirements completely. See it in the attached file
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hey @AlB
It works! I still need to tweak it, like you said, but the data is there.
Thanks for your help.
Try this for the custom column
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.RowCount(Table.SelectRows(Apartamente,
(inner)=> inner[#"Pret cu TVA calculat"]<=[#"Buget maxim disponibil"] and
(if [#"Zone de interes:"]=null then true else if inner[#"Zona"]=null then false else Text.Contains([#"Zone de interes:"], inner[#"Zona"]))
and Text.Contains([#"Cauti un apartament cu ..... camere"], Text.From(inner[#"Numar camere (numar)"]))
and (if List.Contains({null,"nu este important"},[#"Distanta metrou (bifeaza optiunea maxim acceptabila): "]) then true else if inner[#"Distanta fata de metrou"]="Peste 20 minute" then false
else if inner[#"Distanta fata de metrou"] = "peste 20 minute" then false else
if Number.FromText(List.Max(Text.Split(Text.BeforeDelimiter(inner[#"Distanta fata de metrou"]," minute"),"-"))) <= Number.FromText(List.Max(Text.Split(Text.BeforeDelimiter([#"Distanta metrou (bifeaza optiunea maxim acceptabila): "]," minute"),"-"))) then true else false
))))
You might have to tweak a bit to adapt it to your requirements completely. See it in the attached file
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
"It didn't work"
Great. That really is a lot of information to help in looking for the problem
If you just replicated what I did it should work. You are using " minutes"
Number.FromText(Text.BeforeDelimiter([#"Metrou"]," minutes")
Don't you need to adapt that to the language you are using?
Like I said earlier, explain what does not work. Or isolate the several steps in the code to pinpoint which one is not working. Or share the actual pbix or one with dummy data that reproduces the problem
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AlB
Sorry about the lack of details. It's my first message here and I am rather new in working with Power BI.
I've created a pbix file that I am sending here (can't upload it directly)
The first problem was, as you mentioned, not transforming "minutes" to "minute". Now, the 2nd issue has to do with distance to the metro - I actually used a faulty example because in my file, I have intervals of time (e.g. 5-10 minutes), so most probably, this is the last remaining issue.
Also, for the other way round (number of persons that suit each apartment) I should use the same formula?
This is the link to the pbix file
Thank you for your help and I appologize again for my inexactities
Mihai
Can be done in DAX as well following a similar logic. Here is a possible solution in M, where ApartmentsT is the name of your apartments table. See it all at work in the attached file.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0AAIg7aij4KSj4Kyj4AIS1FEwAlKmCrmZeaUlqcVKsTrRSmARuGJXHQUvHQVvkGIQRlVqDBQyhiiFGgrUABIDW4ii1ASrGxDK8/JLFDJzC/KLShLzSsA6TEE6DBEOASnUUTDBqtYMKGqOMBzqNR0FqAZkt8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Budget = _t, Neighbourhoods = _t, #"Number of rooms" = _t, #"Proximity to metro station" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Budget", Int64.Type}, {"Neighbourhoods", type text}, {"Number of rooms", type text}, {"Proximity to metro station", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Numb apartments meeting criteria", each Table.RowCount(Table.SelectRows(ApartmentsT,
(inner)=> inner[Price]<=[Budget] and Text.Contains([Neighbourhoods], inner[Neighbourhood])
and Text.Contains([Number of rooms], Text.From(inner[Number of rooms]))
and (if [Proximity to metro station] = "not important" then true else if inner[Proximity to metro station]="out of range" then false
else if Number.FromText(Text.BeforeDelimiter(inner[Proximity to metro station]," minutes")) <= Number.FromText(Text.BeforeDelimiter([Proximity to metro station]," minutes")) then true else false))))
in
#"Added Custom"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hello @AlB ,
Thanks so much for your answer. I see it is working on your side but I didn't manage to replicate the model in my tables.
Some more details of what I did:
1. I brought through calculated columns the "proximity to metro" and "neighbourhood" into the apartments table (as the information is in a 3rd table named residential projects)
2. In the Users table, I tried to create a new column with the exact code you used and changing the names of the columns, but it didn't work
This is the code I used:
Table.RowCount(Table.SelectRows(#"Apartamente (2)",
(inner)=> inner[#"Pret cu TVA calculat"]<=[#"Buget maxim disponibil"] and Text.Contains([#"Zone de interes:"], inner[#"Zona"])
and Text.Contains([#"Cauti un apartament cu ..... camere"], Text.From(inner[#"Numar camere (numar)"]))
and (if [#"Distanta metrou (bifeaza optiunea maxim acceptabila): "] = "nu este important" then true else if inner[#"Metrou"]="Peste 20 minute" then false
else if Number.FromText(Text.BeforeDelimiter(inner[#"Distanta metrou (bifeaza optiunea maxim acceptabila): "]," minutes")) <= Number.FromText(Text.BeforeDelimiter([#"Metrou"]," minutes")) then true else false)))
Can you please tell me what I did wrong?
Thank you so much for your help
Mihai
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |