Forum Discussion

Ashik008's avatar
Ashik008
Frequent Visitor
2 years ago
Solved

Excel to dax

Hi all,

I need a help with converting a excel formula to dax

=IF(IFERROR(VLOOKUP([@[Service ID]],'VRA Only'!C:AP,12,FALSE),"")=0,"",IFERROR(VLOOKUP([@[Service ID]],'VRA Only'!C:AP,12,FALSE),""))

 

i have service id column in table 1 and table 2, result column  name is "Completed date" in table 2. Please help me to convert the above mentioned formula to dax

Need Help
  • Moetazzahran's avatar
    Moetazzahran
    2 years ago

    Hello Ashik008 , 
    There are three solutions depending on what your need. 

    1- Create a calculated table using DAX 

    Date_Dimension table

    Fact Table

    From your Fact table  & Date_dimension table. You will need to modify the following formula according to your need. However, it should utilize the same structure. 

    Fact with Completed Dates =
    NATURALLEFTOUTERJOIN (
        SELECTCOLUMNS ( 'Fact', "Service ID", 'Fact'[Service ID] & "",'Fact'[Service Name] ),
        SELECTCOLUMNS (
            Date_table,
            "Service ID",  Date_table[Service ID] & "",Date_table[Completed date]
        )
    )

    2-You can use power query. Merge your Fact table with your Date_dimenion table using a Left Outer Join.

     

    3-The final option to use a many-many relationship between both tables (Fact table & Date_dimension table). 

     

    Then use a front-end report table visual and use the column in the fact table and the completed date column from your date_dimension table. 

     

    Let me know if it works. 


    Please let me know if this works for you, and accept it as a solution. Your Kudo is much appreciated.

7 Replies

  • Moetazzahran's avatar
    Moetazzahran
    Resolver II
    2 years ago

    Hello Ashik008 , 

     


    I created two dummy tables in power bi, and created a calculated column that calculates the formula you need to convert into a dax expression with the assumption that you need it as a Calculated column.

    CompletedDate =
    VAR Lookup = LOOKUPVALUE('Date_table'[Completed date], 'Date_table'[Service ID], 'Fact'[Service ID])
    RETURN
    IF(ISBLANK(Lookup), BLANK(), Lookup)



    However, I also created it as a measure 

    Measure_CompletedDate =
    VAR Lookup = LOOKUPVALUE('Date_table'[Completed date], 'Date_table'[Service ID], SELECTEDVALUE('Fact'[Service ID]))
    RETURN
    IF(ISBLANK(Lookup), BLANK(), Lookup)

    Please let me know if it works for you, and accept it as a solution if it serves the purpose. Your kudos is much appreciated 🙂

    • Moetazzahran's avatar
      Moetazzahran
      Resolver II
      2 years ago

      Hello Ashik008 ,
      Service ID only exists in one table, not both table
      Vendor Service IdVendor

      211591ABC
      211591ABC

      While the other table does not have Service ID.
      Assessment TypeAssessment                    Completed Date

      VRA - Initial Risk AssessmentTuesday, August 15, 2023
      VRA - Initial Risk Assessment ImportedWednesday, April 17, 2024


      Is there something wrong with the sample data? Can you please explain?

      • Ashik008's avatar
        Ashik008
        Frequent Visitor
        2 years ago

        service id is available on both table . 

        table is like this 

        service idcompleted date
        211591 
        211591 

        i need the date data in this column

  • Ashik008's avatar
    Ashik008
    Frequent Visitor
    2 years ago

    Moetazzahran hi ,i was getting the blank only with that formula. im pasting the sample of data table

     

    Vendor Service IdVendorAssessment TypeAssessment Completed Date
    211591ABCVRA - Initial Risk AssessmentTuesday, August 15, 2023
    211591ABCVRA - Initial Risk Assessment ImportedWednesday, April 17, 2024
        
  • Moetazzahran's avatar
    Moetazzahran
    Resolver II
    2 years ago

    I am not sure it is clear. 
    I created two data tables within the sample dataset. 

    The first is Date_table which has a unique entries and corresponding dates to each Service ID. 
    The second table is the fact table. where I am using the formula above. 
    Can you please explain how your tables look like? Do service IDs get repeated within the date table? If so, you need to create unique entries for the date table so you would be able to use the Lookupvalue() function. 

    Additionally, please consider creating a relationship between your date mapping table and your fact table. 

     

    CompletedDate =
    RELATED(Date_table[Completed date])
     
    Let me know if it works.

    Please let me know if this solves your problem, if so, please accept it as a solution and your Kudo is much appreciated.

     

     

     

    • Ashik008's avatar
      Ashik008
      Frequent Visitor
      2 years ago

      Moetazzahran sorry for the vague data . let me clear it now 

      service ids are repeating in the date table

      Service idcompleted date
      10112/4/2023
      10112/4/2024
      1021/5/2024
      1036/18/2024
      1035/18/2024
      1044/11/2024

       table 2 say, service data has one column which having the service id.i need to create one column in the service data table , that getting completed date from date table .

      service id
      101
      102
      103
      104