Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I currently have a tabular model measure that:
1. searches for some specific string values at the start of the text value
2. switches specific text values
and returns "Yes" else "No"
This works great however when I apply this measure to a slicer I can't use it. So I'm trying to convert this measure into a calculated column / table to get the desired Yes/No result so they can in turn be used in a slicer.
Shortened version of my working meaure is:
Incidents[Test] =
VAR F_sp = SELECTEDVALUE('Reporting Period Filter'[ReportingPeriod],MAX('Reporting Period Filter'[ReportingPeriod]))
VAR F_rp = SELECTEDVALUE('ReportingPeriod'[ReportingPeriod],F_sp)
VAR r1 = SEARCH("No harm sustained",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r2 = SEARCH("No loss incurred, but could have been",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r4 = SEARCH("Unexpected strike of a utility service, with no damage",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r5 = SEARCH("Working in the vicinity of a major utility asset without permission",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r6 = SEARCH("Working in the vicinity of a utility asset, not in accordance with ABC",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r7 = SEARCH("Tenant Concern - no loss or damage",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r8 = SEARCH("No loss incurred, but could have been",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r9 = SEARCH("Uncovering an unexpected utility service not shown on plans with no damage",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r10 = SEARCH("Property left insecure but no loss",SELECTEDVALUE(Incidents[Actual severity of impact]),True,False)
VAR r3 = SWITCH(
SELECTEDVALUE('Incidents'[Actual severity of impact]),
"Air quality Monitoring not in line", "Yes",
"Breach of soil management requirements not resulting in damage", "Yes",
"Event which resulted in no loss or damage", "Yes",
"False Alarm", "Yes",
"Incident resulting in no environmental harm", "Yes",
"Tenant Reported Near Miss – No Harm But Could Have Been", "Yes",
"Trespassers on site - left of own accord / when asked", "Yes",
"No"
)
var result = if((r1 <> 0|| r2 <> 0|| r4 <> 0|| r5 <> 0|| r6 <> 0|| r7 <> 0|| r8 <> 0|| r9 <> 0|| r10 <> 0),"Yes",r3)
RETURN
CALCULATE(result, KEEPFILTERS(ReportingPeriod[ReportingPeriod] <= F_sp ), KEEPFILTERS(ReportingPeriod[ReportingPeriod] = F_rp ))
However when I apply this formula to a calculated column I get all "No" values
Is it possible to get this measure into a calculated column somehow?
Hi @NiiruK_ ,
According to your statement, I suggest you to try Filter function in your code instead of KEEPFILTERS() function.
If this reply still couldn't help you solve your issue, please share a sample file with me and show me a screenshot with the result you want. This will make it eaiser for me to find the solution.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |