Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
Solved! Go to 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.
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.
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.
@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 |
@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 |
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.
However, I also created it as a measure
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
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?
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
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |