Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Looking for assistance adding a custom calculated column for each row

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!!
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
    d_gosbell
    Icon for Super User rankSuper User
    7 years ago

    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