Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Plates and phone of a user

Hi everyone.

I have some troubles trying to do something in my data.

This is the thing, I have a table like:

PlaceCheckInCarPrice CarPhoneService
California 01/01/2020 14:00AAA2100,000222A
California 01/01/2020 14:00AAA3100,000987A
California 01/01/2020 16:00AAA3150,000222A
California 01/01/2020 16:00ABC260,000223A
New York01/01/2020 16:00TYR260,000123A
New York01/01/2020 20:00TFG460,000567A
New York01/01/2020 21:00  123B
New York01/01/2020 12:00  222B
New York01/01/2020 16:00  567B
San Diego 02/01/2020 16:00AAA3150,000 C
San Diego 03/01/2020 16:00ABC260,000 C
San Diego 04/01/2020 16:00TFG460,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 :

 

PlaceCheckInCarPrice CarPhoneServicePhone2Plate
California 01/01/2020 14:00AAA2100,000222A222AAA2
California 01/01/2020 14:00AAA3100,000987A987AAA3
California 01/01/2020 16:00AAA3150,000222A222AAA3
California 01/01/2020 16:00ABC260,000223A223ABC2
New York01/01/2020 16:00TYR260,000123A123TYR2
New York01/01/2020 20:00TFG460,000567A567TFG4
New York01/01/2020 21:00  123B123TYR2
New York01/01/2020 12:00  222B222AAA3
New York01/01/2020 16:00  567B567TFG4
San Diego 02/01/2020 16:00AAA3150,000 C222AAA3
San Diego 03/01/2020 16:00ABC260,000 C223ABC2
San Diego 04/01/2020 16:00TFG460,000 C567TFG4

 

 

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 🙂 

 

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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]
        )
    )

pp.png

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.

View solution in original post

6 REPLIES 6
v-yingjl
Community Support
Community Support

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]
        )
    )

pp.png

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.

AlB
Super User
Super User

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 

SU18_powerbi_badge

Anonymous
Not applicable

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 

 

SU18_powerbi_badge

Anonymous
Not applicable

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. 

 

Anonymous
Not applicable

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 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.