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 two types of calculated columns I need to create that are essentially lookup values with multiple criteria. I will need 3 of one and 3 of the other type.
The first type looks up a value in a related table based on its ID number and the date. This table has the data set up as follows:
ID | Date | Off? | Hours Ran | Accuracy |
1 | 10/1/2019 | 0 | 12 | 92% |
1 | 11/1/2019 | 1 | 0 | 58% |
1 | 12/1/2019 | 1 | 0 | 52% |
2 | 10/1/2019 | 0 | 1 | 100% |
2 | 11/1/2019 | 0 | 3 | 98% |
2 | 12/1/2019 | 0 | 7 | 85% |
3 | 10/1/2019 | 1 | 0 | 34% |
3 | 11/1/2019 | 0 | 3 | 75% |
3 | 12/1/2019 | 0 | 1 | 100% |
I'm trying to reference this table via calculated columns in a related table that also has Date and ID. Trying to return essentially the value in one of the other three columns based on Date and ID criteria. I know I'm close based on the CALCULATE(FILTER()) combination, but essentially I'm trying to do this in the other table:
Device Off =IF(
CALCULATE(
COUNTROWS(Table[Id]),
FILTER(Table,
Table[Id] = OtherTable[Id]
Table[Date] = OtherTable[Date],
Table[Off] = 1)) > 0, "Off", "On")
Accuracy = IF(
CALCULATE(
COUNTROWS(Table[Id]),
FILTER(Table,
Table[Id] = OtherTable[Id],
Table[Date] = OtherTable[Date],
Table[Accuracy] < .9)) > 0, "Low Accuracy", "")
Low Hours = IF(
CALCULATE(
COUNTROWS(Table[Id]),
FILTER(Table,
Table[Id] = OtherTable[Id],
Table[Date] = OtherTable[Date],
Table[Hours Ran] < 2)) > 0, "Low", "Normal")
The second calculated column is based on a string capture based on ID and Date. If the alert matches the month of the date, I want it to return the alert. Alert table:
ID | Date | Alert | Major Alert | Critical Alert |
1 | 10/20/2019 | Alert | ||
2 | 11/2/2019 | Alert | Critical | |
2 | 12/14/2019 | Major | ||
3 | 11/25/2019 | Alert | Critical | |
3 | 12/13/2019 | Alert |
Each one of these alerts is returned the same way:
Alert = IF(
CALCULATE(
COUNTROWS(AlertTable[Id]),
FILTER(AlertTable,
AlertTable[Id] = OtherTable[Id],
MONTH(AlertTable[Date]) = (MONTH(OtherTable[Date]),
AlertTable[Alert] = "Alert")) > 0, "Alerts", "")
Major Alerts= IF(
CALCULATE(
COUNTROWS(AlertTable[Id]),
FILTER(AlertTable,
AlertTable[Id] = OtherTable[Id],
MONTH(AlertTable[Date]) = (MONTH(OtherTable[Date])
AlertTable[Alert] = "Major")) > 0, "Alerts - Major", "")
Critical Alerts= IF(
CALCULATE(
COUNTROWS(AlertTable[Id]),
FILTER(AlertTable,
AlertTable[Id] = OtherTable[Id]
MONTH(AlertTable[Date]) = (MONTH(OtherTable[Date])
AlertTable[Alert] = "Critical")) > 0, "Alerts - Critical", "")
The desired result would be something like this:
ID | Date | Device Off | Low Hours | Accuracy | Alerts | Major Alerts | Critical Alerts |
1 | 10/1/2019 | On | Normal | Alerts | |||
1 | 11/1/2019 | Off | Low | Low Accuracy | |||
1 | 12/1/2019 | Off | Low | Low Accuracy | |||
2 | 10/1/2019 | On | Low | ||||
2 | 11/1/2019 | On | Normal | Alerts | Alerts - Critical | ||
2 | 12/1/2019 | On | Normal | Low Accuracy | Alerts - Major | ||
3 | 10/1/2019 | Off | Low | Low Accuracy | |||
3 | 11/1/2019 | On | Normal | Low Accuracy | |||
3 | 12/1/2019 | On | Low | Alerts |
I tried the CALCULATE(FILTER()) combinations for each one but I'm not getting that return on my OtherTable. Is there something I'm missing with the filter? Should something other than COUNTROWS() be used since I only need a single value for comparison? Feel like I'm almost there but just need that last bit to make it work out.
Solved! Go to Solution.
Hi @Anonymous ,
Please check if this is what you want:
1. Create OtherTable.
OtherTable = SELECTCOLUMNS ( 'Table', "ID", [ID], "Date", [Date] )
2. Create Calculated columns.
Device Off =
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[ID] = OtherTable[ID]
&& 'Table'[Date] = OtherTable[Date]
&& 'Table'[Off] = 1
)
) > 0,
"Off",
"On"
)
Low Hours =
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[ID] = OtherTable[ID]
&& 'Table'[Date] = OtherTable[Date]
&& 'Table'[Hours Ran] < 2
)
) > 0,
"Low",
"Normal"
)
Accuracy =
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[ID] = OtherTable[ID]
&& 'Table'[Date] = OtherTable[Date]
&& 'Table'[Accuracy] < .9
)
) > 0,
"Low Accuracy",
""
)
Alert =
IF (
CALCULATE (
COUNTROWS ( AlertTable ),
FILTER (
AlertTable,
AlertTable[ID] = OtherTable[ID]
&& MONTH ( AlertTable[Date] ) = MONTH ( OtherTable[Date] )
&& AlertTable[Alert] = "Alert"
)
) > 0,
"Alerts",
""
)
Major Alerts =
IF (
CALCULATE (
COUNTROWS ( AlertTable ),
FILTER (
AlertTable,
AlertTable[ID] = OtherTable[ID]
&& MONTH ( AlertTable[Date] ) = MONTH ( OtherTable[Date] )
&& AlertTable[Major Alert] = "Major"
)
) > 0,
"Alerts - Major",
""
)
Critical Alerts =
IF (
CALCULATE (
COUNTROWS ( AlertTable ),
FILTER (
AlertTable,
AlertTable[ID] = OtherTable[ID]
&& MONTH ( AlertTable[Date] ) = MONTH ( OtherTable[Date] )
&& AlertTable[Critical Alert] = "Critical"
)
) > 0,
"Alerts - Critical",
""
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please check if this is what you want:
1. Create OtherTable.
OtherTable = SELECTCOLUMNS ( 'Table', "ID", [ID], "Date", [Date] )
2. Create Calculated columns.
Device Off =
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[ID] = OtherTable[ID]
&& 'Table'[Date] = OtherTable[Date]
&& 'Table'[Off] = 1
)
) > 0,
"Off",
"On"
)
Low Hours =
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[ID] = OtherTable[ID]
&& 'Table'[Date] = OtherTable[Date]
&& 'Table'[Hours Ran] < 2
)
) > 0,
"Low",
"Normal"
)
Accuracy =
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[ID] = OtherTable[ID]
&& 'Table'[Date] = OtherTable[Date]
&& 'Table'[Accuracy] < .9
)
) > 0,
"Low Accuracy",
""
)
Alert =
IF (
CALCULATE (
COUNTROWS ( AlertTable ),
FILTER (
AlertTable,
AlertTable[ID] = OtherTable[ID]
&& MONTH ( AlertTable[Date] ) = MONTH ( OtherTable[Date] )
&& AlertTable[Alert] = "Alert"
)
) > 0,
"Alerts",
""
)
Major Alerts =
IF (
CALCULATE (
COUNTROWS ( AlertTable ),
FILTER (
AlertTable,
AlertTable[ID] = OtherTable[ID]
&& MONTH ( AlertTable[Date] ) = MONTH ( OtherTable[Date] )
&& AlertTable[Major Alert] = "Major"
)
) > 0,
"Alerts - Major",
""
)
Critical Alerts =
IF (
CALCULATE (
COUNTROWS ( AlertTable ),
FILTER (
AlertTable,
AlertTable[ID] = OtherTable[ID]
&& MONTH ( AlertTable[Date] ) = MONTH ( OtherTable[Date] )
&& AlertTable[Critical Alert] = "Critical"
)
) > 0,
"Alerts - Critical",
""
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, Icey! One thing I also realized that was giving me trouble was the ID fields in each table weren't named the same, so when I referred to them in my formulas, it kept giving me either all or nothing there. It's amazing what a typo can do. Appreciate it!
Hi @Anonymous ,
I don't quite understand it. Please give me some screenshots to help me know your issue.
My understanding is like this, but it's no different.
Best Regards,
Icey
For the first one, you might look at using an "X" aggregation like MAXX, SUMX, etc. over a FILTER of RELATEDTABLE.
for the second one, you should likely look at LOOKUPVALUE.
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |