cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mihai_enache
Frequent Visitor

Count with multiple criteria

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 IDBudgetNeighbourhoodsNumber of roomsProximity to metro station
110000A, B, C, D1, 25 minutes
25000A, E, J, K115 minutes
33000B, C, D, E310 minutes
410000A, B, C, D, E3not important
511000A, E3, 4not important
67000A, B1, 2, 3, 410 minutes

 

 

And one with apartments 

 

Apartment IDPriceNeighbourhoodNumber of roomsProximity to metro station
19750A25 minutes
24000A410 minutes
32550E315 minutes
44550B310 minutes
57550C1out of range
66000A110 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 IDBudgetNeighbourhoodsNumber of roomsProximity to metro stationNumber of apartments according to criterias
110000A, B, C, D1, 25 minutes1 (apt 1,6)
25000A, E, J, K115 minutes0
33000B, C, D, E310 minutes0
410000A, B, C, D, E3not important2 (apt 3,4)
511000A, E3, 4not important2 (apt 2,3)
67000A, B1, 2, 3, 410 minutes3 (apt 2,4,6)
  • in the Apartment table, for each apartment, I want to see how many users are suited for them.
Apartment IDPriceNeighbourhoodNumber of roomsProximity to metro stationNumber of persons according to criterias
19750A25 minutes1 (user 1)
24000A410 minutes2 (users 5, 6)
32550E315 minutes2 (users 4,5)
44550B310 minutes2 (users 4,6)
57550C1out of range0
66000A110 minutes1 (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

2 ACCEPTED SOLUTIONS
AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

 

View solution in original post

AlB
Super User III
Super User III

@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 

SU18_powerbi_badge

View solution in original post

6 REPLIES 6
mihai_enache
Frequent Visitor

Hey @AlB 

 

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

Thanks for your help.

AlB
Super User III
Super User III

@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 

SU18_powerbi_badge

View solution in original post

AlB
Super User III
Super User III

@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 

SU18_powerbi_badge

 

mihai_enache
Frequent Visitor

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

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 

 

SU18_powerbi_badge

 

View solution in original post

mihai_enache
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors