cancel
Showing results for
Did you mean:
Helper V

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

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

1 ACCEPTED SOLUTION
Community Support

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

6 REPLIES 6
Helper V

Guys no suggestions for my query?

Community Support

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

Helper V

Helper V

this is the dashboard needed using the 2 datasets

Super User

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

Helper V

Announcements

#### Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

Top Solution Authors
Top Kudoed Authors
Users online (1,439)