cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sherifff
Helper II
Helper II

Field values from 2nd table is not fetching the exact values.

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.

53 REPLIES 53

Hi @v-kalyj-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-kalyj-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.

vkalyjmsft_1-1639733414577.png

 

Refer to the VALUE function.

 

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

 

 

Hi @v-kalyj-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.

sherifff_0-1639765325694.png

Due to the text values i am getting the below error.

sherifff_1-1639765363136.png

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-kalyj-msft,

 

I hope you got my reply to your earlier post. could you please provide updated formula?

 

Thanks

Hi @v-kalyj-msft , i have updated the post corrected the question 

Hi @v-kalyj-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

 

sherifff_0-1639186630310.png

 

@v-kalyj-msft @Could you please provide your thoughts

 

Thanks

sherifff
Helper II
Helper II

Hi @v-kalyj-msft 

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) 

 

sherifff_2-1638861542474.png

 

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"

 

sherifff_3-1638861566832.png

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!

v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1638769778280.png

 

vkalyjmsft_1-1638769778290.png

 

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

vkalyjmsft_2-1638769778292.png

 

2.Make relationship between the new table and Transaction Table.

vkalyjmsft_3-1638769778293.png

 

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.

vkalyjmsft_4-1638769778296.png

 

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.

vkalyjmsft_5-1638769778298.png

 

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-kalyj-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 NameLabTestTaken DateBP Actual
TEST10010/10/2008125
TEST10010/10/2008126
TEST10010/10/2008125
TEST10012/15/2009121
TEST10012/15/2009121
TEST10012/15/2009123
TEST1005/10/2015141
TEST1005/10/2015142
TEST1005/10/2015141
TEST10010/10/2020130
TEST10010/10/2020131
TEST20010/10/2008131
TEST20010/10/2008125
TEST20010/10/2008126
TEST20010/10/2008126

 

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

TEST10010/10/2008 0:0012570150BP
TEST10010/10/2008 0:0012570150BP
TEST20010/10/2008 0:0012570150BP
TEST20010/10/2008 0:0012570150BP
TEST10012/15/2009 0:0012070150BP
TEST1005/10/2015 0:0014080170BP
TEST10010/10/2008 0:00250100500Cholesterol
TEST10010/10/2008 0:00255100500Cholesterol
TEST20010/10/2008 0:00250100500Cholesterol
TEST10012/15/2009 0:00300100500Cholesterol
TEST10012/15/2009 0:00305100500Cholesterol
TEST1005/10/2015 0:00400200400Cholesterol
TEST20010/10/2008 0:00110150250Sugar
TEST10012/15/2009 0:00180150250Sugar
TEST20010/10/2008 0:00110150250Sugar
TEST10012/15/2009 0:00180150250Sugar
TEST1005/10/2015 0:00105125175Sugar
      

 

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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!