Count with multiple criteria

[mihai_enache]

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:

• in the User table, for each user, I want to see how many apartments suit him

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)

• in the Apartment table, for each apartment, I want to see how many users are suited for them.

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:

• in the user table, the neighborhoods and number of rooms are stacked (see table)
• in the user table, the distance to metro is a maximum so if a user selected 15 minutes, all apartments at 5, 10 and 15 minutes are suited for him. For users who say is not important => they get all apartments. 
• in the apartment table, if an apartment is out of range, he is suitable only to those users who say metro is not important

Thank you for your help

Mihai

[AlB · Accepted solution]

Hi @mihai_enache 

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 

[mihai_enache]

Hey @AlB 

It works! I still need to tweak it, like you said, but the data is there. 

Thanks for your help.

[AlB · Accepted solution]

@mihai_enache 

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 

[AlB]

@mihai_enache 

"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 

[mihai_enache]

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

[AlB · Accepted solution]

Hi @mihai_enache 

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 

[mihai_enache]

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