Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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_id | c_id | update_date | fy_qtr | num_qtr | distinct_qtrs | user_type | fy_qtr_index | not_updated_qrtly |
1 | 15 | 11/1/2021 | FY22-Q2 | 6 | 4 | b | 3 | 1 |
1 | 28 | 5/19/2022 | FY22-Q4 | 6 | 4 | a | 5 | 1 |
1 | 29 | 5/24/2022 | FY22-Q4 | 6 | 4 | a | 5 | 1 |
1 | 35 | 8/12/2022 | FY23-Q1 | 6 | 4 | b | 6 | 1 |
1 | 39 | 1/16/2023 | FY23-Q3 | 6 | 4 | b | 8 | 1 |
Thanks for any/all help!
Ryan
Solved! Go to Solution.
Hi @gopokes0914 ,
I create a table as you mentioned.
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 )
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 )
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.
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
Hi @gopokes0914 ,
I create a table as you mentioned.
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 )
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 )
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
28 | |
23 | |
12 | |
11 | |
10 |