Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
Pleaes help me to create measure to get the below results
Vertical | REPORT_DATE | SLA_MISSES | Current Month & Follow Two Months same value |
BFS | 01-01-2022 | Met | |
BFS | 01-02-2022 | Not Met | 1 |
BFS | 01-03-2022 | Not Met | |
BFS | 01-04-2022 | Not Met | |
CMT | 01-01-2022 | Not Met | 1 |
CMT | 01-02-2022 | Not Met | 1 |
CMT | 01-03-2022 | Not Met | |
CMT | 01-04-2022 | Not Met | |
Digi. Fin. | 01-01-2022 | Met | |
Digi. Fin. | 01-02-2022 | Met | |
Digi. Fin. | 01-03-2022 | Met | |
HC | 01-01-2022 | Met | |
HC | 01-02-2022 | Met | |
HC | 01-03-2022 | Met | |
INS | 01-01-2022 | Met | |
INS | 01-02-2022 | Met | |
INS | 01-03-2022 | Met | |
LS | 01-01-2022 | Met | |
LS | 01-02-2022 | Met | |
LS | 01-03-2022 | Met | |
RHCG | 01-03-2022 | Met |
from the above table of info, I want to count as one, when Current Month and followed by two Months is 'Not Met', Then current month counted as one. else no action.
Here Ref vertical 'CMT' from Jan to Apr, we have all 'not met' only, Considering Jan it should give me a value 1, Because Jan, feb & Mar has same value.
For feb Also 1, Current month Feb is not met and the consecutive 2 months not met, Mar & Apr...then Feb count as 1.
Solved! Go to Solution.
Hi @ssk_1984
Please refer to attached sample file with the solution
Current Month & Follow Two Months same value =
VAR CurrentDate = 'Table'[REPORT_DATE]
VAR CurrentVerticalTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Vertical] ), 'Table'[REPORT_DATE] >= CurrentDate )
VAR T1 = ADDCOLUMNS ( CurrentVerticalTable, "@Rank1", RANKX ( CurrentVerticalTable, [REPORT_DATE],, ASC, Dense ) )
VAR T2 = FILTER ( T1, 'Table'[SLA_MISSES] = "Not Met" )
VAR T3 = ADDCOLUMNS ( T2, "@Rank2", RANKX ( T2, [REPORT_DATE],, ASC, Dense ) )
VAR T4 = FILTER ( T3, [@Rank1] = [@Rank2] )
VAR Result = IF ( COUNTROWS ( T4 ) >= 3, 1 )
RETURN
Result
I have wrongly given the dataset, I working fine...Gr8...Thank you very much
Hi @ssk_1984
Please refer to attached sample file with the solution
Current Month & Follow Two Months same value =
VAR CurrentDate = 'Table'[REPORT_DATE]
VAR CurrentVerticalTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Vertical] ), 'Table'[REPORT_DATE] >= CurrentDate )
VAR T1 = ADDCOLUMNS ( CurrentVerticalTable, "@Rank1", RANKX ( CurrentVerticalTable, [REPORT_DATE],, ASC, Dense ) )
VAR T2 = FILTER ( T1, 'Table'[SLA_MISSES] = "Not Met" )
VAR T3 = ADDCOLUMNS ( T2, "@Rank2", RANKX ( T2, [REPORT_DATE],, ASC, Dense ) )
VAR T4 = FILTER ( T3, [@Rank1] = [@Rank2] )
VAR Result = IF ( COUNTROWS ( T4 ) >= 3, 1 )
RETURN
Result
Thank you for your time,
In this first one coming as wrong, for Dec...i dont have consecutive not met for Jan & Feb..But appearing as one count
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |