Percentage of vendor applications with errors for any time range

[Anonymous]

Hi there,

Here is some demo data to describe my issue:

What I want to do, is for any given date range show the percentage of a Vendor's applications that have at least one Error in the Log table for the specified date range.  In other words, if my date range were to cover all of January 2019 then the result would be:

Infosoft 50%

Pearsoft 0%

This is because there are two ERROR entries for "Spreadshet 500" in January, so that application does have errors, but no entries for "Mail Client 500", therefore 50% of Infosoft's applications in that time period have one or more errors.

If however the filter was changed to just January 1st then the result would be:

Infosoft 0%

Pearsoft 0%

This is because none of the applications logged errors on the 1st January.

Many thanks in advance.

[OwenAuger · Accepted solution]

@Anonymous 

If I assume your tables are named Log, Application and Vendor, you could write a measure like this:

Application % with at least one error = 
VAR ApplicationCount =
    COUNTROWS ( Applications )
VAR ApplicationCountWithError =
    IF (
        ApplicationCount,
        // Add zero to ensure 0% is returned rather than blank in cases with no ERRORs
        CALCULATE ( DISTINCTCOUNT ( 'Log'[ApplicationId] ), 'Log'[EntryType] = "ERROR" ) + 0
    )
RETURN
    DIVIDE ( ApplicationCountWithError, ApplicationCount )

This worked for me in a dummy model - does it work in your model?

Regards,

Owen

[Anonymous]

That works perfectly!  Thank you so much, I really was fumbling around in the dark on this as I am completely new to Power BI. I didn't realize that measures can be written in the form of functions.

Could you just explain why you use the IF clause and how the condition evaluates.  I assume ApplicationCount is an integer value so when you just state:

IF(ApplicationCount, ...

Does this mean "if ApplicationCount is not null" or "if ApplicationCount != 0"?

And what happens if the condition is false, since there is no third IF parameter specified?  Does it make ApplicationCountWithError zero or null? 

Can you also recommend a good book on DAX that would get me to this type of level?

Thank you again.

[OwenAuger]

That's good to hear 🙂

It looks like all of your assumptions are correct 🙂

• On the IF clause, yes you're right, IF ( ApplicationCount,... is the same as IF ( ApplicationCount <> 0... (similar to Excel)
• If the condition is false, the default "value if false" is BLANK. You could provide BLANK () explicitly as the 3rd argument of IF and get the same result. 
• Also BLANK values propogate as BLANKs in divisions and multiplications (but are treated as zero for addition/subtraction), so if ApplicationCount is zero, then the ApplicationCountWithError is BLANK the division on the last line of the measure is also BLANK.

Some books on DAX...I would recommend:

To get started:

• https://exceleratorbi.com.au/learn-to-write-dax/
• https://powerpivotpro.com/the-book/
• https://www.amazon.com/Beginning-DAX-Power-BI-Intelligence/dp/1484234766

More advanced:

• https://www.sqlbi.com/books/the-definitive-guide-to-dax/
• https://www.daxpatterns.com/book/ (website and book)

Regards,

Owen

[Anonymous]

Thank you for explanation on the IF clause, and also thank you for the book recommendations.  I have purchased the Power Pivot Pro book for the starter book, and both the advanced ones you recommended.

Many thanks once again!