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

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