Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |