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

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

Guys any suggestions please

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

@amitchandak any ideas please. Need your help.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors