Measure for counting on column by max value of another column

[HenrikHulstrøm]

Please help me with a measure in F_TEST_COVERAGE table, that counts number of Test_Case_Number by max(Test_Case_Web_Id) where Test_Case_Type=Manual.

With filter for Module_Name set to 24.2, count should be 3 as Test_Case_Id=2 is the current(Max Test_Case_web_Id=1001 for Test_Case_Number).

Tables are:

D_TEST_CASE

TEST_CASE_ID	TEST_CASE_NUMBER	TEST_CASE_WEB_ID	TEST_CASE_TYPE
1	STC0001	1000	Manual
2	STC0001	1001	SemiAutomated
3	STC0001	1002	Automated
4	STC0002	2000	Manual
5	STC0003	3000	Manual
6	STC0004	4000	Manual

D_MODULE

MODULE_ID	MODULE_NAME
1	24.2
2	25.1
3	25.2

F_TEST_COVERAGE

MODULE_ID	TEST_CASE_ID
1	1
1	2
1	4
1	5
1	6
2	1
2	2
2	4
3	1
3	4

[HenrikHulstrøm]

Hi Wilson - thanks for helping out.
For Module_Id=1, Test_Case_Id 1 and 2 are in scope for Test_Case_Number STC0001. Test_Case_Id 2 is the current because it has the highest Test_Case_Web_Id and thus the Test_Case_type is SemiAutomated for STC0001.

[Wilson_]

Henrik,

Why is scope only limited to Test_Case_Number STC0001? Also, why is Test_Case_Id 2 included? Your original post specifically mentions Test_Case_Type = Manual.

[HenrikHulstrøm]

Visualizing it with an example, the steps to find the count are:

Count of current Test_Case_Id (or Test_Case_Number) where Test_Case_Type is Manual, is then 3.

[Wilson_ · Accepted solution]

Henrik,

Okay I think I'm following now.

In order, you are:

• Filtering by MODULE_ID
• Grouping by TEST_CASE_NUMBER
• Filtering for only max TEST_CASE_ID
• Filtering TEST_CASE_TYPE = "Manual"
• Counting number of records

I missed the grouping and also misunderstood the order of operations. Thanks for your patience. 😄

Try:

-- calculates max web ID for each test case number
VAR TestCases = 
ADDCOLUMNS (
    VALUES ( D_TEST_CASE[TEST_CASE_NUMBER] ),
    "Max Web ID", CALCULATE ( MAX ( D_TEST_CASE[TEST_CASE_WEB_ID] ) )
)
-- gets values of all web IDs that are type manual
VAR ManualCases =
CALCULATETABLE (
    VALUES ( D_TEST_CASE[TEST_CASE_WEB_ID] ),
    D_TEST_CASE[TEST_CASE_TYPE] = "Manual"
)
-- filters max web ID table for only manual case types
VAR FilteredCases =
FILTER (
    TestCases,
    [Max Web ID] IN ManualCases
)
-- counts number of records
VAR Result = COUNTROWS ( FilteredCases )

RETURN Result

I've tested this using your own tables (thanks for pasting them as text in your original post instead of as a screenshot, making it easier to copy/paste - much appreciated!). I get 3, 1, and 2 respectively for your three module names.

One important note is I would change the relationship between D_TEST_CASE and F_TEST_COVERAGE to bi-directional. You have test coverage notated as a fact table but it functions more like a bridge table for two tables with a many-to-many relationship. This video from SQLBI on modeling many-to-many relationships might be useful if you haven't already watched it before.

Hope this helps.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[HenrikHulstrøm]

Thanks a lot - spot on! 🤗

[Wilson_]

Perfect, thanks for the update. You're welcome, happy to help. 😄

[Wilson_]

Hi Henrik,

I'm not following your logic. If your module name filter is 24.2, that means module = 1. For module = 1 in F_TEST_COVERAGE, I see test case ID of 1, 2, 4, 5, 6. Of those test case IDs, 1, 4, 5, 6 have a test case type of manual. This corresponds with test case web IDs of 1000, 2000, 3000, 4000. The max of those is 4000.

Where am I going wrong?