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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
gopokes0914
Frequent Visitor

Consecutive Quarter Index by User Type & ID

Hi Everyone,

 

I'm working on a problem that I cannot develop the intended solution for. The problem has to do with remediating exceptions to a business rule, where the Parent ID is unique for an exception and then Child ID reflects each update across the exception timeframe until closure. 

 

There is a field for user category type (type a & b) and I need to make sure users of each type make an update each quarter the exception is open (if an exception spans 2 quarters, I expect 4 Child ID's, one by user type by quarter elapsed). I am translating the subject matter a bit here so let me know if I leave any relevant fields out in this example. 

 

Here are the relevant fields:

>Parent ID
>Child ID
>Status Update (Date)
>Status Update Fiscal Year & Quarter (e.g., FY23Q3)

>Elapsed Quarts for Parent ID (e.g., 6)
>Distinct Quarter for which Update was Made (e.g., 4)

>User category (a or b)
>Fiscal Year Quarter Index (This should have consecutive values with none missing, e.g., 3,4,5,6,7,8)
>Update Comments (Justification for making update)

>Flag for missing quarterly update


Ultimately I need two new columns (Dax columns are easier to validate at the moment), one is a flag for whether user type 'a' failed to make quarterly updates, and another for user type 'b'. Currenlty I am just able to make it work for the entire Parent ID irrespective of which user type failed to make their update.

 

An example of a flagged exception appears below, because it inclusively spans 6 quarters (from 3 to eight) but the distinct quarters in which updates were made is only 4.

 

p_idc_idupdate_datefy_qtr  num_qtrdistinct_qtrsuser_typefy_qtr_indexnot_updated_qrtly
11511/1/2021FY22-Q264b31
1285/19/2022FY22-Q464a51
1295/24/2022FY22-Q464a51
1358/12/2022FY23-Q164b61
1391/16/2023FY23-Q364b81

 

Thanks for any/all help!
Ryan

1 ACCEPTED SOLUTION
v-yilong-msft
Community Support
Community Support

Hi @gopokes0914 ,

I create a table as you mentioned.

vyilongmsft_0-1713922379995.png

Then I create two calculated columns. One for counting a, one for counting b.

UserTypeA_MissingUpdate =
VAR CurrentQuarter = 'Table'[fy_qtr_index]
VAR UserAUpdates =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[user_type] = "a",
        ALL ( 'Table' ),
        'Table'[fy_qtr_index] = CurrentQuarter
    )
RETURN
    IF ( UserAUpdates > 0, 0, 1 )

vyilongmsft_3-1713922811979.png

UserTypeB_MissingUpdate =
VAR CurrentQuarter = 'Table'[fy_qtr_index]
VAR UserBUpdates =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[user_type] = "b",
        ALL ( 'Table' ),
        'Table'[fy_qtr_index] = CurrentQuarter
    )
RETURN
    IF ( UserBUpdates > 0, 0, 1 )

vyilongmsft_2-1713922784498.png

 

 

 

Best Regards

Yilong Zhou

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

2 REPLIES 2
gopokes0914
Frequent Visitor

Hi Yilong,

Thank you sincerely for the quick solution.  It absolutely solved the problem, and I will mark it as such. 
I made a mistake in the translation of the actual problem to how it was represented but the approach you took guided that development, so you actually helped me solve two problems. 

Thanks again,

Ryan

v-yilong-msft
Community Support
Community Support

Hi @gopokes0914 ,

I create a table as you mentioned.

vyilongmsft_0-1713922379995.png

Then I create two calculated columns. One for counting a, one for counting b.

UserTypeA_MissingUpdate =
VAR CurrentQuarter = 'Table'[fy_qtr_index]
VAR UserAUpdates =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[user_type] = "a",
        ALL ( 'Table' ),
        'Table'[fy_qtr_index] = CurrentQuarter
    )
RETURN
    IF ( UserAUpdates > 0, 0, 1 )

vyilongmsft_3-1713922811979.png

UserTypeB_MissingUpdate =
VAR CurrentQuarter = 'Table'[fy_qtr_index]
VAR UserBUpdates =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[user_type] = "b",
        ALL ( 'Table' ),
        'Table'[fy_qtr_index] = CurrentQuarter
    )
RETURN
    IF ( UserBUpdates > 0, 0, 1 )

vyilongmsft_2-1713922784498.png

 

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.