Excel to dax

[Ashik008]

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

[Moetazzahran]

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]

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

service ids are repeating in the date table

Service id	completed date
101	12/4/2023
101	12/4/2024
102	1/5/2024
103	6/18/2024
103	5/18/2024
104	4/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

[Ashik008]

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

Vendor Service Id	Vendor	Assessment Type	Assessment Completed Date
211591	ABC	VRA - Initial Risk Assessment	Tuesday, August 15, 2023
211591	ABC	VRA - Initial Risk Assessment Imported	Wednesday, April 17, 2024

[Moetazzahran]

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]

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

211591	ABC
211591	ABC

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

VRA - Initial Risk Assessment	Tuesday, August 15, 2023
VRA - Initial Risk Assessment Imported	Wednesday, April 17, 2024

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

[Ashik008]

service id is available on both table . 

table is like this 

service id	completed date
211591
211591

i need the date data in this column

[Moetazzahran · Accepted solution]

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.