cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI_Analyticz
Helper V
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 NamePatch MonthCompliant Status
Comp A Comp AJan-22Compliant
Comp B Comp AJan-22Compliant
Comp C Comp AJan-22Compliant
Comp D Comp AJan-22Non-Compliant
Comp E Comp AFeb-22Non-Compliant
  Comp AFeb-22Non-Compliant
  Comp AFeb-22Compliant

 

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_0-1647496089333.png

 

BI_Analyticz_1-1647496128216.png

 

 

 

 

 

 

 

1 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:

15.png

 

Best Regards,
Community Support Team _ Eason

View solution in original post

6 REPLIES 6
BI_Analyticz
Helper V
Helper V

Guys no suggestions for my query?

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:

15.png

 

Best Regards,
Community Support Team _ Eason

BI_Analyticz
Helper V
Helper V

Guys any suggestions please

BI_Analyticz
Helper V
Helper V

@amitchandak 

BI_Analyticz_0-1647500646490.png

this is the dashboard needed using the 2 datasets

amitchandak
Super User
Super User

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

@amitchandak any ideas please. Need your help.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors