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,
Im new to powerbi and coming from tableau.
I wanted to convert a calculation with which Im facing multiple parsing issues as i want a date to be returned and if date is not returned then it has to be a String called Unavailable. This is being combined with few more calculations. Can someone help me frame this in powerbi
Tableau Calculation below :
min
(
if ([Current Location]="Sold" or [Current Location]="Factory")
then
IFNULL
(
STR(IF [Est Deliv Dt]<=TODAY() THEN TODAY()ELSE [Est Deliv Dt] END),'Data Unavailable'
)
ELSE
'N/A'
END
)
Sample dataset
Current Location | Est delive dt |
Sold | 20/09/2018 |
Factory | 18/09/2018 |
Manufacturer | 17/109/2018 |
Sold | 19/09/2018 |
Factory | 16/09/2018 |
Manufacturer | 11/9/2018 |
Solved! Go to Solution.
Hi @Anonymous,
Again, please check this one.
Column 2 = IF ( IF ( Table1[Current Location] = "sold" || Table1[Current Location] = "Factory", TRUE (), FALSE () ) = FALSE (), "NA", IF ( Table1[Est delive dt] < DATE ( 2018, 09, 18 ), "Data Unavailable", "" & CALCULATE ( MIN ( Table1[Est delive dt] ), ALLEXCEPT ( Table1, Table1[Current Location] ) ) ) )
Also please find the file attached.
Regards,
Frank
Hi @Anonymous,
I made one sample for your reference. Here I create the measure as below.
Measure = IF ( IF ( MAX ( Table1[Current Location] ) = "Sold" || MAX ( Table1[Current Location] ) = "Factory", TRUE (), FALSE () ) = FALSE (), IF ( MAX ( Table1[Est delive dt] ) <= TODAY (), TODAY (), MAX ( Table1[Est delive dt] ) ), "Data Unavailable" )
For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly tell me what is your excepted result.
Regards,
Frank
Hi Frank,
Thanks for your reply.
However this calculation return the following error :
Also on line no 9, isnt that supposed to be true() instead of false()?
Just trying to understand the logic.
Incase the logic was confusing let me interpret that in words:
When current location = Sold or factory only then
do the below :
if date < today() then show todays date
else( which means date is not < today) show min value of all the dates
in all other cases(when there is no date at all) show "data is unavailable"
finally if current location is not sold or factory then show
"NA"
Sample Output :
Current Location | Est delive dt | Expected Output | Comments |
Factory | 22/09/2018 | 21/09/2018 | assumed today() is 18/09/2018 |
Factory | 21/09/2018 | Expected Output column will be used on Tooltip later | |
Manufacturer | 17/09/2018 | NA | since current location is not sold or factory |
Manufacturer | 11/9/2018 | NA | since current location is not sold or factory |
Sold | Data Unavailable | If the date was less than 18/09/2018(today), we would have displayed that date here | |
Sold | Data Unavailable |
Hi @Anonymous,
Again, please check this one.
Column 2 = IF ( IF ( Table1[Current Location] = "sold" || Table1[Current Location] = "Factory", TRUE (), FALSE () ) = FALSE (), "NA", IF ( Table1[Est delive dt] < DATE ( 2018, 09, 18 ), "Data Unavailable", "" & CALCULATE ( MIN ( Table1[Est delive dt] ), ALLEXCEPT ( Table1, Table1[Current Location] ) ) ) )
Also please find the file attached.
Regards,
Frank
Your new calculation by logic should work but i dont know why it is still showing the parsing error.
I see that the date is converted into string by using ""
Hi @Anonymous,
That should be the issue of your column [Est delive dt], that should be in the format of date. Please change the data type of the column and try again, if it doesn't work, Could you please share your pbix to me?
Regards,
Frank
Hi Frank,
This worked perfect now.
Thanks a ton.
Also just wanted to know since the highlighted info is repeated twice, can I "group by" this info ?
Hi @Anonymous,
Do you mean when you create the column using the formula as I offered you got this error message? Or do you want to do some calculation using the calculated Column? As you know, it is a text column, so you cannot do that.
Regards,
Frank
Hi Frank,
The calculation that you offered shows this error.
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |