Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

8 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 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
      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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