Help with Complex Measures.

Anonymous — Mon, 08 Nov 2021 11:56:33 GMT

Hi, I am very new to Power BI but have been learning quickly until I got into this issue.

Please be gentle on me as I am still new and may not know some of the basics.

Background:

I have around 60,000 records in a table that I pull using many SQL joins. As the filters for my visuals are complicated, I created custom columns for filters using and/or conditions. Let's say for visual 1, if all the conditions are satisfied then [Custom_Column_1] will be "True" else "False".

It works fine until here.

Problem:

I have three columns in the main table after using Custom_Filter_1 = "True"

ID,
IDType,
SubmittedDate

Also, I have Calendar table with columns -

Date,
DateNext,
IsWeekDay,
IsHoliday,
IsToday,
IsTomorrow

In the main data, ID Type has Mod and Remod options.

Mod has 1 Day of SLA and Remod has 2 days of SLA. These tasks need to be completed before SLA.

For both the ID Types, I need to check if the IDs are in SLA or out of SLA, even if one ID is out of SLA, then it should be "No". Then, I need to count the number of IDs due today and tomorrow based on SLA considering workdays (Calendar table has [IsWeekDay] and [IsHoliday]).

I get this data when Custom_Filter_1 is added to the visual level filter:

ID	ID Type	SubmittedDate
1258	Mod	11/4/2021
1217	Remod	11/4/2021
1499	Remod	11/4/2021
1418	Remod	11/5/2021
1351	Mod	11/4/2021
1333	Mod	11/5/2021
1269	Mod	11/5/2021
1318	Mod	11/5/2021
1146	Mod	11/5/2021
1117	Mod	11/5/2021
1404	Remod	11/5/2021
1206	Remod	11/5/2021
1439	Mod	11/5/2021
1155	Remod	11/5/2021
1197	Mod	11/8/2021
1338	Mod	11/8/2021
1198	Remod	11/8/2021
1146	Remod	11/8/2021
1168	Remod	11/8/2021
1126	Remod	11/8/2021
1289	Remod	11/9/2021
1355	Mod	11/9/2021
1016	Mod	11/9/2021

Calander Table:

Date	DateNext	IsWeekDay	IsHoliday	IsToday	IsTomorrow
11/1/2021	11/2/2021	1	0	FALSE	FALSE
11/2/2021	11/3/2021	1	0	FALSE	FALSE
11/3/2021	11/4/2021	1	0	FALSE	FALSE
11/4/2021	11/5/2021	1	0	FALSE	FALSE
11/5/2021	11/6/2021	1	0	FALSE	FALSE
11/6/2021	11/7/2021	0	0	FALSE	FALSE
11/7/2021	11/8/2021	0	0	FALSE	FALSE
11/8/2021	11/9/2021	1	0	TRUE	FALSE
11/9/2021	11/10/2021	1	0	FALSE	TRUE
11/10/2021	11/11/2021	1	0	FALSE	FALSE

Required Output

Task	SLA	In SLA (Y/N)	Due Today	Total Due Today	Due Tomorrow	Total Due Tomorrow
Mod	1 Day	No	2	10	2	12
Remod	2 Days	Yes	4	11	1	11

If [IsWeekDay] = 0 or if [IsHoliday] = 1, then it should be counted as working day and it shouldn't be counted in SLA days.
SLA is fixed. 1 days for Mod and 2 days for Remod.
For Mod, SLA is 1 day. The task has to be completed before SubmitedDate+1 day. If all the ID Numbers are completed on or before SubmittedDate +1 then "Yes", else "No". The tasks on 11/4/2021 were not completed by 11/5/2021. So it is not in SLA.
For Remod, the SLA is 2 days. If no ID number exceeds 2 days from SubmittedDate, then it is in SLA.
The Tasks 11/4/2021 are in SLA because 6 and 7 were not working days. So, 11/4/2021 tasks can be completed by 11/8/2021.
Due Today = Count of IDs that are pending today. i.e. 11/8/2021
Total Due Today = Total count of IDs that are pending until today. i.e. on or before 11/08/2021

I really appreciate your help.

Re: Help with Complex Measures.

lbendlin — Sun, 26 Dec 2021 16:20:21 GMT

@Anonymous You mark 11/8/2021 as "today" yet you have tasks submitted on 11/9/2021 ?

topic Re: Help with Complex Measures. in Custom Visuals Development Discussion

Help with Complex Measures.

Re: Help with Complex Measures.