Count all rows and also all blanks rows of Intersect measure

[Anonymous]

Hi guys,

I've created a measure in order to intersect columns of a PowerBI report of which I cannot use Powerquery due to orginasational restrictions.
In this measure the Main Event is displayed with the adhering Findings. 

Measure:

Event_findings_# =

VAR Events =

SELECTCOLUMNS('QHSE Event Fact Current',"QHSE Event Key", 'QHSE Event Fact Current'[QHSE Event Key] ) --returns a 1-column table with all the event keys for the selected workflow

VAR Findings =

SELECTCOLUMNS(CALCULATETABLE('QHSE Finding Fact Current', ALL('QHSE Workflow')), "QHSE Event Key", 'QHSE Finding Fact Current'[QHSE Event Key] ) --returns a 1-column table with all the event keys found in the finding fact, ignoring the filter set on the QHSE Workflow table

VAR final =

INTERSECT ( Findings, Events ) --'QHSE event key's found in the unfiltered Findings table also found in the filtered Event table

VAR Result =

COUNTROWS ( final )

RETURN

Result

Which looks like this:

The office audits column above displays the Event and the column 'Findings' is displaying the number of findings that came out of the Audit. This number can be eg 15, 3, 0 etc. 

Now what I want to achieve is to display how many Events of the selected period have a Finding (any Finding so doesn't matter the number of Findings). And also how many Events have no Finding at all. 
 

I tried to calculate it numorous times for example with COUNTX in the below measure:

Total Office events with findings = COUNTX('QHSE Event Fact Current',[Event_findings_#])

But that did not provide the right result, it's missing some data highlighted in yellow:

The measure to count the blanks I'm completely lost. I tried several times with COUNTBLANK however it needs to connect to the measure 'Event_findings_#' and that is something I did not achieve yet. 

I think I need to create a measure just like 'Event_findings_#'  to display the correct result but for now I did not succeed yet. 

Hope someone could help me solve it!

[v-cazheng-msft]

Hi @Anonymous 

You can try this Measure.

Total Office events with findings =

VAR filtered =

    FILTER ( 'QHSE Event Fact Current', NOT ( ISBLANK ( [Findings] ) ) )

RETURN

    COUNTROWS ( filtered )

If it doesn't work, please let me known and please provide some sample data without sensitive data in the form of table.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!