CODEWITHSUNDEEP
powerbidax
broot19
broot19

Reputation: 1

Finding a count of a relationship between two different columns

I have the following sample spreadsheet, titled "Error", linked to Power BI.

Image 1

What I need to do is figure out how many times a certain title corresponds with a certain severity. If I were to do this correctly in this case, the output would be something like the following:

Memory Usage/Warning: 4

Memory Usage/Critical: 1

Event Log: Application/Information: 2

Disk Usage(E:)/Warning: 2

I currently have one DAX function that looks like the following:

Memory Usage/Warning = COUNTROWS(FILTER(GROUPBY(Error, Error[Title], Error[Severity]), Error[Title] = "Memory Usage"))

which gives me:

Memory Usage/Warning: 2

I'm aware that I might need to use multiple functions to get the outputs I want so any help with even one would be greatly appreciated.

Upvotes: 0

Views: 415

Answers (2)

smpa01
smpa01

Reputation: 4346

Stack strongly recommends putting table and not picture.

Also, while working with DAX the rule of thumb is to use explicit measure (authored) and not implicit measure (auto-generated).

If your table has only two columns, then use this measure

Measure =
CALCULATE (
    COUNT ( 'Table'[Title] ), //4. get the count of the join of 2 and 3
    ALL ( 'Table' ), // 1. remove whatever is currently visible
    VALUES ( 'Table'[Severity] ), //2. what is currently visible
    VALUES ( 'Table'[Title] ) // 3. what is currently visible
)

If your table has more than just these two columns, then use this measure

Measure =
CALCULATE (
    //2 gets count by partition
    COUNT ( 'Table'[Severity] ),
    //1 define the partition
    ALLEXCEPT ( 'Table', 'Table'[Title], 'Table'[Severity] )  
)

Upvotes: 1

Strictly Funk
Strictly Funk

Reputation: 358

Here's an easy solution that doesn't use DAX formulas: You can show counts for each combination using a matrix visual.

  • Rows = Title
  • Columns = Severity
  • Values = Count of Severity

result

Upvotes: 1

Related Questions