Create a summary dashboard with max occurrence of status in matrix table-Most Complex ever designed

[BI_Analyticz]

I have 2 datasets

1. List if distinct PC Names

2. List of the same PC names with Compliant status for every month. For some PC's there will be no status for a particular month. In that case we have to consider those as Compliant automatically.

Datasets.

Dataset 1		Dataset 2
PC Name		PC Name	Patch Month	Compliant Status
Comp A		Comp A	Jan-22	Compliant
Comp B		Comp A	Jan-22	Compliant
Comp C		Comp A	Jan-22	Compliant
Comp D		Comp A	Jan-22	Non-Compliant
Comp E		Comp A	Feb-22	Non-Compliant
		Comp A	Feb-22	Non-Compliant
		Comp A	Feb-22	Compliant

I looking for a resultant dashboard as below or a new dataset as shown in another screenshot. I am kind of stuck since this is a pretty large database with 25 lakh rows. So I am stuck to decide the best practice for this dashboard requirement. How can I achieve this...?

[BI_Analyticz]

Guys no suggestions for my query?

[v-easonf-msft · Accepted solution]

Hi, @BI_Analyticz 

You need to create a seperated table for field 'Patch Month'。

Patch Month = DISTINCT('Dateset 2'[Patch Month])

Then try formula as below:
calculated columns in dataset2

Count1 = CALCULATE (COUNTROWS ( 'Dateset 2' ),ALLEXCEPT ('Dateset 2','Dateset 2'[PC Name],'Dateset 2'[Patch Month],'Dateset 2'[Compliant Status]))

Count2 = CALCULATE (MAX ( 'Dateset 2'[Count1] ),ALLEXCEPT ( 'Dateset 2', 'Dateset 1'[PC Name], 'Dateset 2'[Patch Month] ))

max occurrence of status = 
CALCULATE (
    MAX ( 'Dateset 2'[Compliant Status] ),
    FILTER (
        'Dateset 2',
        'Dateset 2'[PC Name] = EARLIER ( 'Dateset 2'[PC Name] )
            && 'Dateset 2'[Patch Month] = EARLIER ( 'Dateset 2'[Patch Month] )
            && 'Dateset 2'[Count1] = 'Dateset 2'[Count2]
    )
)

measure:

M_Compliant Status = 
VAR _status =
    CALCULATE (
        Max ( 'Dateset 2'[max occurrence of status] ),
        FILTER (
            'Dateset 2',
            'Dateset 2'[PC Name] = SELECTEDVALUE ( 'Dateset 1'[PC Name] )
                && 'Dateset 2'[Patch Month] = SELECTEDVALUE ( 'Patch Month'[Patch Month] )
        )
    )
RETURN
    IF ( ISBLANK ( _status ), "Compliant", _status )

Result：

Best Regards,
Community Support Team _ Eason

[BI_Analyticz]

Guys any suggestions please

[BI_Analyticz]

@amitchandak 

this is the dashboard needed using the 2 datasets

[amitchandak]

@BI_Analyticz , second and thrid dashboard are matrix and table display using the 2 tables in first screenshot(or Data) 

[BI_Analyticz]

@amitchandak any ideas please. Need your help.