Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Experts,
I have created a new PBI dashboard data coming from 2 SQL Tables. I have created a merge query where it will have the data with the selected fields from both the tables. In this 3rd Query Table I want to the date field to be mapped and looked up from the 2nd table where it will have unique entries (by combining 2 fields) . I want to get the related columns from the matching records.
Here are little more details about my current scenario
Table 1 - SQL Table (Transaction Table) - It has all the transactions (LabTest) data
Table 2 - SQL Table (Master Table)- It has the Master Data where it has the results expected range data (Minimum Range Value, Maximum Range Value)
Table 3 - Query Table (Results Table)- I have created a query table by using the Outer merge using the Key Field called "Test Name".
I have a scenario where there are about 10 to 20 columns data to be fetched from the 2nd table (Master Table) into 1st Table (Transaction Table)
The criteria is , for a given record In the transaction table for selected Column "Test Name" ('s field value) it should look for "LabTestTaken Date" Field , Need to check in the Master table if the "Test Name" and the "LabTestTaken Date" fall under/within the date range on the fields of Table 2 (Master Table) called "Start Date" and "End Date" Field.
If "yes" then i need to bring the values of the 10 to 20 fields from that Master table from the matched record.
Note: in the Master Table (Table 2) each "Test Name" is having multiple rows, for example
Test Name Start Date End Date BP_Min BP_Maximum
Test 100 1/1/2000 12/31/2010 70 150
TEST 100 1/1/2011 06/25/2018 80 170
TEST 100 06/26/2018 04/09/2020 90 180
TEST 100 04/10/2020 null (means currently active)
(If you notice the TEST 100 is having 4 entries it is purely based on 4 unique Start and End Date ranges)
The expected results on the transaction table is as follows
Test Name LabTestTaken Date BP Actual BP_Min BP_Maximum
Test 100 10/10/2008 125 70 150
TEST 100 12/15/2009 120 70 150
Test 100 05/10/2015 140 80 170
Test 100 10/10/2020 120 90 180
If you notice from the above transaction table the first 2 rows are falling under the first date range which has the BP_Minimum as 70 and BP_Maximum as 150
But currently I am getting all the random values , it just goes and fetches the values from row 1 thorugh 4 and provided the wrong values.
I am sure, there is something on the relationship i need to take care of which i am missing. I hope someone can help me in this one.
Hi @v-yanjiang-msft , Thanks for your reply. My data source is from SQL table. Could you please guide me how i can do it with SQl table.
Thanks.
Hi @v-yanjiang-msft ,
I have accepted the solution, In my Max data i have few more combinations, which if i explain will go more complex hence i haven't mentioned earlier, i will revisit the code and if required will reachout to you. The other issue since it is realted I feel it will be easy for you to give your sugestions.
There is a one more column called "Lab Location" , there is a need to show the total of "Test100" grouped by "Lab Location" , The main point here is, all the BP_Min , BP_Max, etc., all are of Data type "String". As for some of the tests , they do mark it as "Not applicable", "Special Conditions" unfortnately in these fields. So i cannot convert them directly into numbers.
Is that possible to calculate without making over burden on the tables, by using DAX.
Example
Test Name Lab Location Lab Test taken Date Actual BP_Min Actual_Group Total BP_Group total
Test100 EAST 1/1/2000 90. 70 185. 150
Test100 EAST 6/30/2019. 95. 80 185. 150
Test100 North 1/1/2000 110. 100 240. 220
Test100 North 06/25/2016. 130. 120 240. 220
for East Group the total comes to 150
And for North - 220
for BP_Group Total, for East Group the total comes to 150
And for North - 220
and for Actual_Group total
for East Group the total comes to 185
And for North - 240
Could you plz help me in this calculations.
Hi @sherifff ,
You can use the VALUE function in the DAX, like this:
BP Group total =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Lab location] = MAX ( 'Table'[Lab location] )
),
VALUE ( 'Table'[BP_Min] )
)
Actual Group total =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Lab location] = MAX ( 'Table'[Lab location] )
),
VALUE ( 'Table'[Actual] )
)
Get the expected result.
Refer to the VALUE function.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
Hi @v-yanjiang-msft,
Thanks for sharing the formula, but in the requirment as i have mentioned the Actual and Bp_Min columns have text values in it, which i don't see in your file. It is how it looks at my end on the table.
Due to the text values i am getting the below error.
How do we calculate just the number values , filtering out and ignoring any text values in the column and the rest of the condition is same as mentioned in the earlier post?
Hi @v-yanjiang-msft,
I hope you got my reply to your earlier post. could you please provide updated formula?
Thanks
Hi @v-yanjiang-msft,
Thanks for providing the updated code, i tested it out it works great. I have one more situation which is tied to this issue, where there is a special tests which has spl start and end dates. The values for the Min and Max should show based on this (comparing these) special dates.
Could you please help me with senario as well, I really appreciate your help.
Below screen would give details
Please refer the attached screen shots. If you look at the transaction table., thats the final output i am looking at.
Screen 1: The master table. Here the scenario is it has
1) Same "TestName" but with different start and end dates
2) The "Min" and "Max" for [BP], [Sugar] and [Cholesterol] have values which is again might be different based on Start and End dates (some will have same Min and Max values even with different start and end dates)
The Below screen is of the Transaction Table.
Here i need to bring the Minimum and Maximum values from Master table.
The condition it has to match is as follows
The criteria is , for a given record In the transaction table for "Test Name" ('s field value) it should look for "LabTestTaken Date" Field , Need to check in the Master table if the "Test Name" and the "LabTestTaken Date" fall under/within the date range on the fields of Table 2 (Master Table) called "Start Date" and "End Date" Field.
The part i missed was the "Test Type" is another criteria, which it should bring the values of BP Min and Max values if it is for BP as the "Test Type" , and same way it should bring for other values (Sugar_Min, Sugar_Max , Cholesterol_Min and Cholesterol_Max) all these values should be displayed under Minimum and Maximum columns (Refer column "D" and Column "E"
I hope i have explained it what i am looking for. Please let me know if you need any futher information.
Thank you so much!
Hi @sherifff ,
According to your description, if you use merge query like bellow, as in the merge, it will only correspond to matching columns, and will not calculate whether LabTestTaken Date is between Start Date and End Date, so the result is that all time periods in the Master Table will appear in the same LabTestTaken Date.
My solution is to achieve through DAX function.
1.Create a new table with all columns in Master Table you want appearing in the result, I add another test name in the Master Table called TEST200.
Table =
SUMMARIZE (
'Master Table',
'Master Table'[Test Name],
'Master Table'[BP_Min],
'Master Table'[BP_Max],
'Master Table'[Start Date],
'Master Table'[End Date]
)
2.Make relationship between the new table and Transaction Table.
3.Create a measure in Transaction Table.
BP_Actual =
IF (
MAX ( 'Transaction Table'[Test Name] ) = MAX ( 'Table'[Test Name] )
&& MAX ( 'Transaction Table'[LabTestTaken Date] ) > MAX ( 'Table'[Start Date] )
&& MAX ( 'Transaction Table'[LabTestTaken Date] ) < MAX ( 'Table'[End Date] ),
MAX ( 'Transaction Table'[BP Actual] ),
BLANK ()
)
4.Get the expected result by putting these elements into the visual like me.
5.In your sample, the LabTestTaken Date in the 4th row of result table correspondence to the fourth time period of the Master table which has no value of BP_Min and BP_Max, so there is no such one in my result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yanjiang-msft , Thank you very much for taking time and going over my issue and providing the detailed steps and the file.
I have 2 more senarios associted with my issue, which i forgot to mention in my initial request.
1) The Transaction Table has the "LabTestTaken Date" also has duplicate values and also the BP Actual Value also has duplicate values (Please refer the below table).
Test Name | LabTestTaken Date | BP Actual |
TEST100 | 10/10/2008 | 125 |
TEST100 | 10/10/2008 | 126 |
TEST100 | 10/10/2008 | 125 |
TEST100 | 12/15/2009 | 121 |
TEST100 | 12/15/2009 | 121 |
TEST100 | 12/15/2009 | 123 |
TEST100 | 5/10/2015 | 141 |
TEST100 | 5/10/2015 | 142 |
TEST100 | 5/10/2015 | 141 |
TEST100 | 10/10/2020 | 130 |
TEST100 | 10/10/2020 | 131 |
TEST200 | 10/10/2008 | 131 |
TEST200 | 10/10/2008 | 125 |
TEST200 | 10/10/2008 | 126 |
TEST200 | 10/10/2008 | 126 |
2) In the Transaction Table there are other columns which will have actual values like for the tests "Sugar" , "Cholesterol". The requirement is to have one "Actual Value" Column and based on the Test Type the actual value should display. Similarly for the "Min" and "Max" Columns as well, should display the values based on the Test Type and it should also staisfy the dates range.
Test Name LabTestTakenDate Actual Min Max Test Type
TEST100 | 10/10/2008 0:00 | 125 | 70 | 150 | BP |
TEST100 | 10/10/2008 0:00 | 125 | 70 | 150 | BP |
TEST200 | 10/10/2008 0:00 | 125 | 70 | 150 | BP |
TEST200 | 10/10/2008 0:00 | 125 | 70 | 150 | BP |
TEST100 | 12/15/2009 0:00 | 120 | 70 | 150 | BP |
TEST100 | 5/10/2015 0:00 | 140 | 80 | 170 | BP |
TEST100 | 10/10/2008 0:00 | 250 | 100 | 500 | Cholesterol |
TEST100 | 10/10/2008 0:00 | 255 | 100 | 500 | Cholesterol |
TEST200 | 10/10/2008 0:00 | 250 | 100 | 500 | Cholesterol |
TEST100 | 12/15/2009 0:00 | 300 | 100 | 500 | Cholesterol |
TEST100 | 12/15/2009 0:00 | 305 | 100 | 500 | Cholesterol |
TEST100 | 5/10/2015 0:00 | 400 | 200 | 400 | Cholesterol |
TEST200 | 10/10/2008 0:00 | 110 | 150 | 250 | Sugar |
TEST100 | 12/15/2009 0:00 | 180 | 150 | 250 | Sugar |
TEST200 | 10/10/2008 0:00 | 110 | 150 | 250 | Sugar |
TEST100 | 12/15/2009 0:00 | 180 | 150 | 250 | Sugar |
TEST100 | 5/10/2015 0:00 | 105 | 125 | 175 | Sugar |
Could you please provide some insights on how this can be acheived. Thanks a lot for your help.
Hi @sherifff ,
I'm sorry for that I'm not very clear about your senarios 2, and what is your finnal expected result.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |