Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone.
I have some troubles trying to do something in my data.
This is the thing, I have a table like:
Place | CheckIn | Car | Price Car | Phone | Service |
California | 01/01/2020 14:00 | AAA2 | 100,000 | 222 | A |
California | 01/01/2020 14:00 | AAA3 | 100,000 | 987 | A |
California | 01/01/2020 16:00 | AAA3 | 150,000 | 222 | A |
California | 01/01/2020 16:00 | ABC2 | 60,000 | 223 | A |
New York | 01/01/2020 16:00 | TYR2 | 60,000 | 123 | A |
New York | 01/01/2020 20:00 | TFG4 | 60,000 | 567 | A |
New York | 01/01/2020 21:00 | 123 | B | ||
New York | 01/01/2020 12:00 | 222 | B | ||
New York | 01/01/2020 16:00 | 567 | B | ||
San Diego | 02/01/2020 16:00 | AAA3 | 150,000 | C | |
San Diego | 03/01/2020 16:00 | ABC2 | 60,000 | C | |
San Diego | 04/01/2020 16:00 | TFG4 | 60,000 | C |
Place: location
CheckIn: when a person use the service
Car: plate
Price Car: price of the car related to the plate
Phone: user phone
Service: there are 3 different services as A, B, C.
What I want to do is the get two new colums like :
Place | CheckIn | Car | Price Car | Phone | Service | Phone2 | Plate |
California | 01/01/2020 14:00 | AAA2 | 100,000 | 222 | A | 222 | AAA2 |
California | 01/01/2020 14:00 | AAA3 | 100,000 | 987 | A | 987 | AAA3 |
California | 01/01/2020 16:00 | AAA3 | 150,000 | 222 | A | 222 | AAA3 |
California | 01/01/2020 16:00 | ABC2 | 60,000 | 223 | A | 223 | ABC2 |
New York | 01/01/2020 16:00 | TYR2 | 60,000 | 123 | A | 123 | TYR2 |
New York | 01/01/2020 20:00 | TFG4 | 60,000 | 567 | A | 567 | TFG4 |
New York | 01/01/2020 21:00 | 123 | B | 123 | TYR2 | ||
New York | 01/01/2020 12:00 | 222 | B | 222 | AAA3 | ||
New York | 01/01/2020 16:00 | 567 | B | 567 | TFG4 | ||
San Diego | 02/01/2020 16:00 | AAA3 | 150,000 | C | 222 | AAA3 | |
San Diego | 03/01/2020 16:00 | ABC2 | 60,000 | C | 223 | ABC2 | |
San Diego | 04/01/2020 16:00 | TFG4 | 60,000 | C | 567 | TFG4 |
Service A has all the data I want, but service B does not have Car, I want that the most expensive car shows in the column. Then, for service C it does not have the phone, I want that it shows the phone. As you can see there are two phones with the same car, I want that everyphone gets a car.
Maybe its not possible to have the most expensive car, but maybe by the last car used.
Thank you for your help 🙂
Solved! Go to Solution.
Hi @Anonymous ,
You can create these two columns:
Phone2 =
VAR tab =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Phone] <> BLANK () ),
'Table'[CheckIn],
'Table'[Phone],
'Table'[Car]
)
RETURN
IF (
NOT ( ISBLANK ( [Phone] ) ),
[Phone],
MAXX (
FILTER (
tab,
[Phone] IN DISTINCT ( 'Table'[Phone] )
&& [Car] = EARLIER ( 'Table'[Car] )
&& [CheckIn]
= MAXX (
FILTER (
tab,
[Phone] IN DISTINCT ( 'Table'[Phone] )
&& [Car] = EARLIER ( 'Table'[Car] )
),
[CheckIn]
)
),
[Phone]
)
)
Plate =
VAR tab =
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
'Table'[Car] <> BLANK ()
&& 'Table'[Price Car] <> BLANK ()
),
'Table'[Car],
'Table'[Price Car],
'Table'[Phone]
)
RETURN
IF (
[Car] <> BLANK (),
[Car],
MAXX (
FILTER (
tab,
[Phone] = EARLIER ( 'Table'[Phone] )
&& [Price Car]
= MAXX ( FILTER ( tab, [Phone] = EARLIER ( 'Table'[Phone] ) ), [Price Car] )
),
[Car]
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You can create these two columns:
Phone2 =
VAR tab =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Phone] <> BLANK () ),
'Table'[CheckIn],
'Table'[Phone],
'Table'[Car]
)
RETURN
IF (
NOT ( ISBLANK ( [Phone] ) ),
[Phone],
MAXX (
FILTER (
tab,
[Phone] IN DISTINCT ( 'Table'[Phone] )
&& [Car] = EARLIER ( 'Table'[Car] )
&& [CheckIn]
= MAXX (
FILTER (
tab,
[Phone] IN DISTINCT ( 'Table'[Phone] )
&& [Car] = EARLIER ( 'Table'[Car] )
),
[CheckIn]
)
),
[Phone]
)
)
Plate =
VAR tab =
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
'Table'[Car] <> BLANK ()
&& 'Table'[Price Car] <> BLANK ()
),
'Table'[Car],
'Table'[Price Car],
'Table'[Phone]
)
RETURN
IF (
[Car] <> BLANK (),
[Car],
MAXX (
FILTER (
tab,
[Phone] = EARLIER ( 'Table'[Phone] )
&& [Price Car]
= MAXX ( FILTER ( tab, [Phone] = EARLIER ( 'Table'[Phone] ) ), [Price Car] )
),
[Car]
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Your description is rather cryptic. I've been able to understand and create the column Plate (with the most expensive car). For the Phone2 column, though, you'll have to try and explain better the logic behind. What happens when the car has more than 2 phone numbers, as it is the case for AAA3. Why is 222 chosen instead of 987???
Plate =
IF (
NOT ISBLANK ( Table1[Car] ),
Table1[Car],
VAR maxPrice_ =
MAXX (
CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[Phone] ) ),
Table1[Price Car]
)
RETURN
CALCULATE (
DISTINCT ( Table1[Car] ),
ALLEXCEPT ( Table1, Table1[Phone] ),
Table1[Price Car] = maxPrice_
)
)
See it at work in the attached file. Make sure the blanks in the Car column are actually blanks, and not spaces or zero-lenght strings, since the code checks for blanks
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
I have another issue, what if I want in the column plate just the last plate used by the user, not based on price.
@Anonymous
Where is the user in the data? Please provide an example to clarify
You haven´t explained how the logic for phone 2 works. I repeat the question for column Phone 2: What happens when the car has more than 2 phone numbers, as it is the case for AAA3. Why is 222 chosen instead of 987???
If you explain that properly I'll be able to put the Phone 2 column together
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
The user is in the phone number. There is another table of users, with phone as primary Key, but in only has antoher column with the date of register.
For the case of AAA3 I'd like that 222 and 987 have the same plate.
yeah, it is little complicated.
It is an app for mobility, service A is a parking at a city, service B is a mall and service C is at private club.
Think in this way, maybe I go to a private club and a machine reads my plate, but It does not have information of the person, only the plate. I have information of the plate in service A, so in some way I know who's the person.
Antoher issue is that maybe my dad when to the city and register his phone with the same car.
Thank you for the plate column it worked
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |