Count rows that don't exist.

[nezzzz]

Hello, first ever post here, hope I'm posting this in the right forum. I am having a difficult time with the creation of a measure for a specific scenario. I have the below two tables, the first one is TestCases which captures multiple testcases and the Folder they are stored into. The second table is TestCasesHistory which displays the dates when a test case was run and the verdict for each one of them.

There is also a Date table connected to TestCasesHistory.

The request, for me at least, is kind of difficult. I need to display a stacked bar chart showing all test folders and the % of each Verdict. If for example, we filter on Folder A, between 1-May and 5 May, we would see 50% Pass and 50% Fail. I was able to do this, but the tricky part is that I also need to represent the 'No result' status which represents the Test cases that did not have a result between the filtered dates. In the example below, we see that TC1 had no results on 2nd, 4th May and TC2 had no results on 2nd, 3rd, 4th, 5th May. I have no idea how to count the rows where the result date is missing, given that there is no row in the table for it.

TestCases:

TestCaseID	TestFolderID
TC1	A
TC2	A
TC3	B
TC4	C

TestCasesHistory:

TestCaseID	Result_Date	Verdict
TC1	1-May-2024	Fail
TC1	3-May-2024	Pass
TC1	5-May-2024	Pass
TC2	1-May-2024	Fail

Or... if there is a way to create a table in report view where if I filter between 1st - 5th May, to display both TC1 and TC2 and all 5 dates and where there are no results, to display the 'No Result' status for the respective dates. 

Any advice would be really helpful. Thank you!

[v-nuoc-msft]

Hi @nezzzz 

@Irwan Thank you very much for your prompt reply.

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

[nezzzz]

Hey @Irwan,

Thanks a lot for your reply!

I don't want to have the Result_Date in the chart (so the chart is going to look like your first screenshot), but in that chart, I would also like to have the % of No results. And yes, the % will be modified once 'No results' will be added. But as mentioned, I don't know how to count the number of days where there are no entries. 

[Irwan · Accepted solution]

Hello @nezzzz 

if you dont want Result_Date in chart, I assumed it might be TestCaseID as you want to see those verdict number.

Both TC3 and TC4 have no result so it is empty (I believe you can not show it as 'No Result" value in visualization so "No Result" means empty value).

for this, create a measure with following DAX:

Verdict Calculation =

var _Verdict = SELECTEDVALUE('TestCasesHistory'[Verdict])

Return

IF(

    ISBLANK(_Verdict),

    "No Value",

    DISTINCTCOUNT('TestCasesHistory'[Verdict])

)

then create a visual with 100% Stacked Bar.

After that, you can plot those value into the visual

Hope this will help you.

Thank you.

[Irwan]

Hello @nezzzz 

trying to understand what your goal.

You said 1-May to 5-May is 50% Pass and 50% Fail and you said you want to show "No-Result" on 2-May and 4-May.

Do you want to find total value or distribute the value based on date?

When you didnt put Result_Date value in your stacked visual, then yes you got 50%-50% which is calculating total value.

But as soon as you put Result_Date value, it will separate those value based on date. Value on 1-May is 2 because there are two values on 1-May (TC1 Fail and TC2 Fail).

is this what you need by showing as "No-Result"? Value on 2-May and 4-May are blank.

Hope this will help you.

Thank you.