Looking for assistance adding a custom calculated column for each row

[Anonymous]

I have a unique ticket identifier in another table which then breaks out each critical application into individual columns to measure TRUE/FALSE on whether the application was impacted during the incident.

Here is an example of a similar table (call it table 1):

TickName|CreateDate|App1|App2|App3|App4

1              | 1/1/2000  |True  |False |True  |False
2              | 1/2/2000  |True  |True  |False |False

3              | 1/3/2000  |False |True  |False |False

Because I cannot have an organized graph showing a break down of my critical applications with total impacts, I have a new table (Call it table 2) created called Critical apps and is currently setup with one column as:

CritApp|
App 1   |
App 2   |
App 3   |
App 4   |

I have an expression to calculate the number of true/false for each application as follows:

App 1 True Count = CALCULATE(COUNTROWS(FILTER('Table 1','Table 1'[App 1] = TRUE())))
Return is 2

App 2 True Count = CALCULATE(COUNTROWS(FILTER('Table 1','Table 1'[App 2] = TRUE())))

Return is 2

App 3 True Count = CALCULATE(COUNTROWS(FILTER('Table 1','Table 1'[App 3] = TRUE())))

Return is 1

App 4 True Count = CALCULATE(COUNTROWS(FILTER('Table 1','Table 1'[App 4] = TRUE())))

Return is 0

How would I be able to populate those values into Table 2 to look as follows:

CritApp| Count
App 1   | 2
App 2   | 2
App 3   | 1
App 4   | 0

((And of course would recalculate as data gets added/updated))

Thanks in advance!!

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

You may try to unpivot the table 1 in edit queries, and then create a relationship with table 2 as below:

Step1:

In Edit Queries, select all "App" columns, then unpivot these columns.

https://radacad.com/pivot-and-unpivot-with-power-bi

Step2:

Create a relationship between table1 and table 2.

Step3:

Create a measure by this formula:

Measure = CALCULATE(COUNTROWS(FILTER('Table 1','Table 1'[Value]= TRUE())))+0

Result:

and here is my pbix file, please try it.

Best Regards,

Lin

[d_gosbell]

I would pivot "table 1" as follows in Power Query and only keep the "true" rows, then you can join "table 2" on the new application column and your measure could be a distinctcount of TickName and it will work across dates and applications. (and adding new applications will not need a change in your model and measures)

TickName|CreateDate|Application

1              | 1/1/2000  |App1
1              | 1/1/2000  |App3
2              | 1/2/2000  |App1
2              | 1/2/2000  |App2

3              | 1/3/2000  |App2