Trying to return average of true or false based on higher number of each

[DeckTeck365]

Hi,

In PowerBI I'm trying to create a report that returns the average of true or false based on higher number of each. Basically it's IT based for whether the Anti Virus definition status is up to date on various machines.

I'm filtering for the last 7 dates and that returns a table with up to 7 rows per machine. I would like to create a measure for example that would assess whether the count of "true" or "false" for the definitions being up to date is higher per machine and return just that value or "true" if they are equal in number.

For example. Today being the 12th Dec 2018 there will be a row per different machine with either true or false each day. If AV updates 4 days out of 7 then I want it to return values of true in just one row.

Visaully I mean something like the below with table 1 having al lthe data and the 2nd one being the output report.

E.g.

CustomerName	DeviceName	Date	DefinitionsUpToDate
ACME	ACME1	06/12/2018	TRUE
ACME	ACME1	07/12/2018	TRUE
ACME	ACME1	08/12/2018	TRUE
ACME	ACME1	09/12/2018	TRUE
ACME	ACME1	10/12/2018	TRUE
ACME	ACME1	11/12/2018	FALSE
ACME	ACME1	12/12/2018	TRUE
ACME	ACME2	06/12/2018	TRUE
ACME	ACME2	07/12/2018	TRUE
ACME	ACME2	08/12/2018	FALSE
ACME	ACME2	09/12/2018	FALSE
ACME	ACME2	10/12/2018	FALSE
ACME	ACME2	11/12/2018	FALSE
ACME	ACME2	12/12/2018	TRUE
ACME	ACME3	06/12/2018	TRUE
ACME	ACME3	07/12/2018	TRUE
ACME	ACME3	08/12/2018	TRUE
ACME	ACME3	09/12/2018	FALSE
ACME	ACME3	10/12/2018	FALSE
ACME	ACME3	11/12/2018	TRUE
ACME	ACME3	12/12/2018	TRUE
CustomerName	DeviceName	Date	DefinitionsUpToDate
ACME	ACME1	12/12/2018	TRUE
ACME	ACME2	12/12/2018	FALSE
ACME	ACME3	12/12/2018	TRUE

I know that the date column may also be an issue as what date does it put in the column after the measure is applied if it's an aggregate?

Many Thanks,

Declan

[v-piga-msft]

Hi @DeckTeck365,

You could create a calculated table with formula below to achieve your desired output.

Table 2 =
SUMMARIZE (
    'Data',
    'Data'[CustomerName],
    'Data'[DeviceName],
    "maxdate", CALCULATE (
        MAX ( 'Data'[Date] ),
        ALLEXCEPT ( Data, Data[CustomerName], 'Data'[DeviceName] )
    ),
    "DefinitionsUpToDate", IF (
        CALCULATE (
            COUNTROWS ( FILTER ( 'Data', 'Data'[DefinitionsUpToDate] = TRUE () ) ),
            ALLEXCEPT ( Data, 'Data'[CustomerName], Data[DeviceName] )
        )
            > 4,
        TRUE (),
        FALSE ()
    )
)

Here is the output.

Best  Regards,

Cherry

[DeckTeck365]

Hi Cherry,

Thanks so much for that , I really appreciate it. 

I am running it in DirectQuery mode from a SQL server and it looks like you can't create a Calculated Table that way. 

Might need to be a query I run on the SQL server and then DirectQuery the output?

Thanks 

Declan

[DeckTeck365]

Actually just noticed that part of the feature update for November 2018's version of PowerBI desktop is " Composite models, which allow you to combine direct query and import sources together in one model, is now generally available." So I'll try that! 

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2018-feature-summary/

Thanks 

Declan

[v-piga-msft]

Hi @DeckTeck365,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry