topic Measure for counting on column by max value of another column in DAX Commands and Tips

Measure for counting on column by max value of another column

HenrikHulstrøm — Thu, 08 Aug 2024 13:17:41 GMT

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

Re: Measure for counting on column by max value of another column

Wilson_ — Thu, 08 Aug 2024 17:53:14 GMT

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?

Re: Measure for counting on column by max value of another column

HenrikHulstrøm — Fri, 09 Aug 2024 08:17:46 GMT

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.

Re: Measure for counting on column by max value of another column

Wilson_ — Fri, 09 Aug 2024 20:43:24 GMT

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.

Re: Measure for counting on column by max value of another column

HenrikHulstrøm — Mon, 12 Aug 2024 09:02:37 GMT

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.

Re: Measure for counting on column by max value of another column

Wilson_ — Mon, 12 Aug 2024 15:24:30 GMT

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.

Re: Measure for counting on column by max value of another column

HenrikHulstrøm — Tue, 13 Aug 2024 08:09:25 GMT

Thanks a lot - spot on! 🤗

Re: Measure for counting on column by max value of another column

Wilson_ — Tue, 13 Aug 2024 12:53:38 GMT

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