cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors