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.

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