cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Parsing issues with calculation

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 LocationEst delive dt
Sold20/09/2018
Factory18/09/2018
Manufacturer17/109/2018
Sold19/09/2018
Factory16/09/2018
Manufacturer11/9/2018
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Parsing issues with calculation

Hi @leharkapil,

 

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] )
            )
    )
)

2.PNG

 

Also please find the file attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

8 REPLIES 8
Highlighted
Community Support
Community Support

Re: Parsing issues with calculation

Hi @leharkapil,

 

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"
)

 Capture.PNG

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Post Patron
Post Patron

Re: Parsing issues with calculation

Hi Frank,

 

Thanks for your reply.

However this calculation return the following error : 

 

Untitled.png

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 LocationEst delive dtExpected OutputComments
Factory22/09/201821/09/2018assumed today() is 18/09/2018
Factory21/09/2018 Expected Output column will be used on Tooltip later
Manufacturer17/09/2018NAsince current location is not sold or factory
Manufacturer11/9/2018NAsince current location is not sold or factory
Sold Data UnavailableIf the date was less than 18/09/2018(today), we would have displayed that date here
Sold Data Unavailable
Highlighted
Community Support
Community Support

Re: Parsing issues with calculation

Hi @leharkapil,

 

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] )
            )
    )
)

2.PNG

 

Also please find the file attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Parsing issues with calculation

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 "" 

 

Untitled.png

 

Highlighted
Community Support
Community Support

Re: Parsing issues with calculation

Hi @leharkapil,

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Post Patron
Post Patron

Re: Parsing issues with calculation

Hi Frank,

 

The calculation that you offered shows this error.

Highlighted
Community Support
Community Support

Re: Parsing issues with calculation

Hi @leharkapil,

 

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?

 

21.PNG

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Post Patron
Post Patron

Re: Parsing issues with calculation

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 ?

 

Untitled.png

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors