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.
Hi, I have a switch function as follows:
EstDesc = Switch ( TRUE (),
WorkRequest[CurrentEstimate] = 97,"Small",
WorkRequest[CurrentEstimate] = 497,"Medium",
WorkRequest[CurrentEstimate] = 997,"Large",
WorkRequest[CurrentEstimate] = 1997,"X-Large")
I want to add a catch all to the end that would just return WorkRequest[CurrentEstimate], but the below is throwing an error.
EstDesc = Switch ( TRUE (),
WorkRequest[CurrentEstimate] = 97,"Small",
WorkRequest[CurrentEstimate] = 497,"Medium",
WorkRequest[CurrentEstimate] = 997,"Large",
WorkRequest[CurrentEstimate] = 1997,"X-Large",
WorkRequest[CurrentEstimate])
Error is: Expressions that yield variant data type cannot be used to define calculated columns. Please advise. Thanks
Solved! Go to Solution.
Hi @delia,
That's a limitation of Direct Query. Please try it like below.
EstDesc = SWITCH ( TRUE (), WorkRequest[CurrentEstimate] = 97, "Small", WorkRequest[CurrentEstimate] = 497, "Medium", WorkRequest[CurrentEstimate] = 997, "Large", WorkRequest[CurrentEstimate] = 1997, "X-Large", "" & WorkRequest[CurrentEstimate] )
Best Regards,
Dale
Hi @delia,
The WorkRequest[CurrentEstimate] must be a numerical column. The root cause is the type of the results are mismatched, Text and Numbers. You can modify it like below.
EstDesc = SWITCH ( TRUE (), WorkRequest[CurrentEstimate] = 97, "Small", WorkRequest[CurrentEstimate] = 497, "Medium", WorkRequest[CurrentEstimate] = 997, "Large", WorkRequest[CurrentEstimate] = 1997, "X-Large", FORMAT ( WorkRequest[CurrentEstimate], "" ) )
Best Regards,
Dale
Hi @v-jiascu-msft, you are correct that WorkRequest[CurrentEstimate] is a numerical column. I tried using the FORMAT function (thanks for the suggestion) but received this error:
Function FORMAT is not allowed as part of calculated column DAX expressions on DirectQuery models.
Hi @delia,
That's a limitation of Direct Query. Please try it like below.
EstDesc = SWITCH ( TRUE (), WorkRequest[CurrentEstimate] = 97, "Small", WorkRequest[CurrentEstimate] = 497, "Medium", WorkRequest[CurrentEstimate] = 997, "Large", WorkRequest[CurrentEstimate] = 1997, "X-Large", "" & WorkRequest[CurrentEstimate] )
Best Regards,
Dale
Awesome, that did it! Thanks Dale!!! 🙂
I do have another small issue though....while the values all display now, the number values show up with a ".0" at the end. Since this new calculated column contains both text and numbers, I cannot use the modeling feature in Power BI to update it to a Whole Number. Any thoughts on how to get rid of the decimal at the end for WorkRequest[CurrentEstimate]? Thank u.
Hi @delia,
Does WorkRequest[CurrentEstimate] have decimals? I can't reproduce it. Can you share some snapshots?
Best Regards,
Dale
Hi Dale, I should explain better. WorkRequest[CurrentEstimate] is a calculated column with Data Type currently set as Decimal. Its value is derived from columns whose Data Type is specified as Decimal. Is it okay if I change the Data Type of WorkRequest[CurrentEstimate] to a Whole Number? That should affect the Data Type of the other columns it is derived from right? Thanks again.
Hi @delia,
According to my test, the Date type Whole number will truncate the decimals. For instance, 121.33 will be 121. You can do it if the degree of accuracy isn't a big deal. There could be a workaround: creating anther column.
Best Regards,
Dale
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |