Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Ashik008
Frequent Visitor

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

1 ACCEPTED SOLUTION

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

1- Create a calculated table using DAX 

Moetazzahran_1-1718739674508.png

Date_Dimension table

Moetazzahran_2-1718739742427.png

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]
    )
)

Moetazzahran_0-1718739562675.png

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

Moetazzahran_3-1718739978315.png

Moetazzahran_4-1718740018357.png

 

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. 

 

Moetazzahran_5-1718740192895.png

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.

View solution in original post

7 REPLIES 7
Moetazzahran
Resolver II
Resolver II

I am not sure it is clear. 
I created two data tables within the sample dataset. Moetazzahran_1-1718700860724.pngMoetazzahran_0-1718700849893.png

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. 

 

Moetazzahran_2-1718701208556.png

Moetazzahran_3-1718701248019.png

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.

 

 

 

@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
Ashik008
Frequent Visitor

@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
Resolver II
Resolver II

Hello @Ashik008 , 

Moetazzahran_1-1718666037500.png

 


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)

Moetazzahran_0-1718666013298.png

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 🙂

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?

service id is available on both table . 

table is like this 

service idcompleted date
211591 
211591 

i need the date data in this column

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

1- Create a calculated table using DAX 

Moetazzahran_1-1718739674508.png

Date_Dimension table

Moetazzahran_2-1718739742427.png

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]
    )
)

Moetazzahran_0-1718739562675.png

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

Moetazzahran_3-1718739978315.png

Moetazzahran_4-1718740018357.png

 

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. 

 

Moetazzahran_5-1718740192895.png

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.