Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nezzzz
Frequent Visitor

Count rows that don't exist.

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:

TestCaseIDTestFolderID
TC1A
TC2A
TC3B
TC4C

 

TestCasesHistory:

TestCaseIDResult_DateVerdict
TC11-May-2024Fail
TC13-May-2024Pass
TC15-May-2024Pass
TC21-May-2024Fail

 

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!

 

4 REPLIES 4
v-nuoc-msft
Community Support
Community Support

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
Frequent Visitor

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
Solution Supplier
Solution Supplier

Hello @nezzzz 

 

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

 

Irwan_0-1720660833911.png

 

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
Irwan_1-1720661026142.png

 

Hope this will help you.

Thank you.

Irwan
Solution Supplier
Solution Supplier

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.

Irwan_1-1720579854584.png

 

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).

Irwan_2-1720579944393.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.