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
FRG
Resolver I
Resolver I

Calculated measure filtered by many columns

HI,

In this table I want to calculate the volume of product used by a user. We see the initial volume in [pmp_total] and this pmp decrease when the user is working in [pmp_prod].
So for each distinct [nopile] and each distinct noopt (user ID), I need to know how many volume.

For this I think I have to use the column «Type» who give me when a new [nopile] (PILEIN) and when the user is replaced (NEWOPT).
In this case, the user 53 use 582 - 510 = 72. 
The user 52 use 510 - 258 = 252 and the user 27 use 258 - 1= 257.

Capture.JPG

1 ACCEPTED SOLUTION

Hi,

I found an easier way to do this. In power query I added an index and a caluclated column to get the expecting result:

 

Capture.JPG

 

Thanks

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

Hi @FRG,

 

I think you can write a formula to compare 'pmp_total' and maximum 'pmp_prod' volume of current user, if it less than 'pmp_total', find out the diff between maximum 'pmp_prod' of current user and minimum 'pmp_prod' of next user; otherwise get the diff between 'pmp_total' and first 'pmp_prod' of current user.

 

Sample measure:

Measure =
VAR currUser =
    SELECTEDVALUE ( Table[Noopt] )
VAR currJob =
    SELECTEDVALUE ( Table[noprodjob] )
VAR pmp_prodlist =
    CALCULATETABLE (
        VALUES ( Table[pmp_prod] ),
        FILTER ( ALLSELECTED ( Table ), [noopt] = currUser && [noprodjob] = currJob )
    )
VAR pmp_total =
    MAXX ( FILTER ( ALLSELECTED ( Table ), [noprodjob] = currJob ), [pmp_total] )
VAR maxOfCurrent =
    MAXX ( pmp_prodlist, [pmp_prod] )
RETURN
    IF (
        maxofCurrent < pmp_total,
        MINX (
            FILTER (
                ALLSELECTED ( Table ),
                [noprodjob] = currJob
                    && [pmp_prod] > maxOfCurrent
            ),
            [pmp_prod]
        )
            - maxOfCurrent,
        pmp_total - MINX ( pmp_prodlist, [pmp_prod] )
    )

 

Regards,

Xiaxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Wow, what a complex formula!

Except for user #53 I got negative number if I filter this nopile. For this user the result is exact.

Thanks

Hi @FRG,

 

It sounds like I miss some conditions to ignore calculation on some scenarios. Can you please share a pbix file with part of sample data for test and modify formula?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @FRG,

 

You can try to use below measure if it works, I modify my logic and add 'pmp_total' as one of filter condition:

Measure = 
VAR currUser =
    SELECTEDVALUE ( 'sysebout_jour piletrx'[Noopt] )
VAR currJob =
    SELECTEDVALUE ( 'sysebout_jour piletrx'[noprodjob] )
VAR pmp_total =
    MAX ( 'sysebout_jour piletrx'[pmp_total] )
VAR pmp_prodlist =
    CALCULATETABLE (
        VALUES ( 'sysebout_jour piletrx'[pmp_prod] ),
        FILTER (
            ALLSELECTED ( 'sysebout_jour piletrx' ),
            [noopt] = currUser
                && [noprodjob] = currJob
                && [pmp_total] = pmp_total
        )
    )
VAR maxOfCurrent =
    MAXX ( pmp_prodlist, [pmp_prod] )
VAR minOfCurrent =
    MINX ( pmp_prodlist, [pmp_prod] )
RETURN
    IF (
        maxofCurrent < pmp_total,
        MINX (
            FILTER (
                ALLSELECTED ( 'sysebout_jour piletrx' ),
                [noprodjob] = currJob
                    && [pmp_total] = pmp_total
                    && [pmp_prod] > maxOfCurrent
            ),
            [pmp_prod]
        )
            - minOfCurrent,
        pmp_total - MINX ( pmp_prodlist, [pmp_prod] )
    )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi,

I found an easier way to do this. In power query I added an index and a caluclated column to get the expecting result:

 

Capture.JPG

 

Thanks

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.