Hi
I want to achieve the below with DAX commands
1) Avg Value: Display the Average value of KPI IDx (x = 1, 2 ... N) from Table 1 based on a KPI selected @ run time. The User selected KPI @ run time will be based on KPI Name field from Table 3 and it has to be selected from a slicer. It has be a dynamic dax calculation, considering the fact that there are huge number of KPI columns and the list in Table 3 can grow
2) Count Value: Count the number of times KPI IDx (x = 1, 2 ... N) from Table 1 is above or below a user defined threshold. The user defined threshold will be based on Threshold field from Table 3 and the above or below condition will be based on the Sign field from Table 3
Table 1 & Table 2 are linked in the Model based on Joiner Field. the Joiner field is uinque in Table 1
Table 1
| Joiner Field | Attribute 1 | Attribute 2 | …. | Attribute n |
| A | | | | |
| B | | | | |
| C | | | | |
| D | | | | |
| | | | | |
| | | | | |
Table 2
| Joiner Field | Date | KPI ID 1 | KPI ID 2 | …. | KPI ID n |
| A | 01.01.2020 | 98 | 0.3 | | |
| A | 02.01.2020 | 95 | 0.7 | | |
| A | 03.01.2020 | 93 | 0.8 | | |
| B | 01.01.2020 | 92 | 0.2 | | |
| B | 02.01.2020 | 92 | 0.1 | | |
| B | 03.01.2020 | 91 | 0.1 | | |
| C | 01.01.2020 | 98 | 0.9 | | |
| C | 02.01.2020 | 99 | 1.5 | | |
| C | 03.01.2020 | 98 | 1.2 | | |
| .. | | | | | |
| .. | | | | | |
Table 3
| KPI ID | KPI Name | Threshold | Sign |
| KPI 1 | xxxxx | 95 | < |
| KPI 2 | yyyyy | 0.5 | > |
| ... | | | |
| KPI n | zzzzz | 98 | < |
Ouput Examples
User Selected KPI Name = xxxx
| Joiner Field (Table 1) | Attribute 1 | Attribute 2 | …. | Attribute n | KPI (Avg Value) | KPI (Count) |
| A | | | | | 95.33 | 2 |
| B | | | | | 91.66 | 3 |
| C | | | | | 98.33 | 0 |
| D | | | | | | |
| | | | | | | |
User Selected KPI Name = yyyy
| Joiner Field (Table 1) | Attribute 1 | Attribute 2 | …. | Attribute n | KPI (Avg Value) | KPI (Count) |
| A | | | | | 0.6 | 2 |
| B | | | | | 0.13 | 0 |
| C | | | | | 1.2 | 3 |
| D | | | | | | |
| | | | | | | |
Floyd
