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

Moetazzahran's avatar
Moetazzahran
Icon for Resolver II rankResolver 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
Icon for Resolver II rankResolver 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

    • Moetazzahran's avatar
      Moetazzahran
      Icon for Resolver II rankResolver II
      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.