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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sherifff
Helper III
Helper III

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-yanjiang-msft , Iam stuck with this special condition  which i have posted last week, where it has different start and end times columns for tests  with special test numbers. The values need to be shown in the same min and max column.  These special tests are defined in a column called "Test Category" the values are "Yes" and "No".

 

Could you please help me with this one. 

Thanks

Hi @sherifff ,

According to your condition, I modify the formula, like this:

Min =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
VAR _T2 =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Sql_Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[Sql_End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
RETURN
    IF (
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            ),
            'Master'[Sql_Start Date]
        )
            = BLANK (),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                > MAXX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[BP_Min]
                    ),
                "Cholesterol",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Cholesterol_Min]
                    ),
                "Sugar",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sugar_Min]
                    )
            ),
            IF (
                MAX ( 'Transaction'[Lab Test Taken Date] )
                    < MINX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Start Date]
                    ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[BP_Min]
                        ),
                    "Cholesterol",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Cholesterol_Min]
                        ),
                    "Sugar",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Sugar_Min]
                        )
                ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Min] ), _T ),
                    "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Min] ), _T ),
                    "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Min] ), _T )
                )
            )
        ),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                > MAXX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Sql_Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[BP_Min]
                    ),
                "Cholesterol",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Cholesterol_Min]
                    ),
                "Sugar",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sugar_Min]
                    )
            ),
            IF (
                MAX ( 'Transaction'[Lab Test Taken Date] )
                    < MINX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sql_Start Date]
                    ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[BP_Min]
                        ),
                    "Cholesterol",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Cholesterol_Min]
                        ),
                    "Sugar",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Sugar_Min]
                        )
                ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Min] ), _T2 ),
                    "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Min] ), _T2 ),
                    "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Min] ), _T2 )
                )
            )
        )
    )
Max =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] )
    )
VAR _T2 =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Sql_Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[Sql_End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
RETURN
    IF (
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            ),
            'Master'[Sql_Start Date]
        )
            = BLANK (),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                > MAXX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[BP_Max]
                    ),
                "Cholesterol",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Cholesterol_Max]
                    ),
                "Sugar",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sugar_Max]
                    )
            ),
            IF (
                MAX ( 'Transaction'[Lab Test Taken Date] )
                    < MINX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Start Date]
                    ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[BP_Max]
                        ),
                    "Cholesterol",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Cholesterol_Max]
                        ),
                    "Sugar",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Sugar_Max]
                        )
                ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Max] ), _T ),
                    "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Max] ), _T ),
                    "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Max] ), _T )
                )
            )
        ),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                > MAXX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Sql_Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[BP_Max]
                    ),
                "Cholesterol",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Cholesterol_Max]
                    ),
                "Sugar",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sugar_Max]
                    )
            ),
            IF (
                MAX ( 'Transaction'[Lab Test Taken Date] )
                    < MINX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sql_Start Date]
                    ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[BP_Max]
                        ),
                    "Cholesterol",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Cholesterol_Max]
                        ),
                    "Sugar",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Sugar_Max]
                        )
                ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Max] ), _T2 ),
                    "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Max] ), _T2 ),
                    "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Max] ), _T2 )
                )
            )
        )
    )

Get the expected result.

vkalyjmsft_0-1639475650838.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-yanjiang-msft ,
Thanks for sharing the formula but i am not getting the correct results.  The reason is while explaining earlier, I missed to mention the Spl_Start Date doesn't have any empty values. All the rows has got values (sorry about that).  In your condition i noticed that you are performing the calculations based on the above condition. In the the below screen i am trying to show you how the master data looks on the Start Date values (Both Start Date and Spl_Start Date) 

 

 

sherifff_2-1639552359433.png

Could you please help me with the updated formula. Thanks.

 

 

 

Hi @sherifff ,

I modify the formula below:

Min =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
VAR _T2 =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Sql_Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[Sql_End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
RETURN
    IF (
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            ),
            'Master'[Start Date]
        )
            = BLANK (),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                > MAXX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Sql_Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[BP_Min]
                    ),
                "Cholesterol",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Cholesterol_Min]
                    ),
                "Sugar",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sugar_Min]
                    )
            ),
            IF (
                MAX ( 'Transaction'[Lab Test Taken Date] )
                    < MINX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sql_Start Date]
                    ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[BP_Min]
                        ),
                    "Cholesterol",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Cholesterol_Min]
                        ),
                    "Sugar",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Sugar_Min]
                        )
                ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Min] ), _T2 ),
                    "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Min] ), _T2 ),
                    "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Min] ), _T2 )
                )
            )
        ),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                > MAXX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[BP_Min]
                    ),
                "Cholesterol",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Cholesterol_Min]
                    ),
                "Sugar",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sugar_Min]
                    )
            ),
            IF (
                MAX ( 'Transaction'[Lab Test Taken Date] )
                    < MINX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Start Date]
                    ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[BP_Min]
                        ),
                    "Cholesterol",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Cholesterol_Min]
                        ),
                    "Sugar",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Sugar_Min]
                        )
                ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Min] ), _T ),
                    "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Min] ), _T ),
                    "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Min] ), _T )
                )
            )
        )
    )
Max =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] )
    )
VAR _T2 =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Sql_Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[Sql_End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
RETURN
    IF (
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            ),
            'Master'[Start Date]
        )
            = BLANK (),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                > MAXX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Sql_Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[BP_Max]
                    ),
                "Cholesterol",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Cholesterol_Max]
                    ),
                "Sugar",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sugar_Max]
                    )
            ),
            IF (
                MAX ( 'Transaction'[Lab Test Taken Date] )
                    < MINX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sql_Start Date]
                    ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[BP_Max]
                        ),
                    "Cholesterol",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Cholesterol_Max]
                        ),
                    "Sugar",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Sugar_Max]
                        )
                ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Max] ), _T2 ),
                    "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Max] ), _T2 ),
                    "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Max] ), _T2 )
                )
            )
        ),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                > MAXX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[BP_Max]
                    ),
                "Cholesterol",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Cholesterol_Max]
                    ),
                "Sugar",
                    MAXX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Sugar_Max]
                    )
            ),
            IF (
                MAX ( 'Transaction'[Lab Test Taken Date] )
                    < MINX (
                        FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                        'Master'[Start Date]
                    ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[BP_Max]
                        ),
                    "Cholesterol",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Cholesterol_Max]
                        ),
                    "Sugar",
                        MINX (
                            FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                            'Master'[Sugar_Max]
                        )
                ),
                SWITCH (
                    MAX ( 'Transaction'[Test Type] ),
                    "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Max] ), _T ),
                    "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Max] ), _T ),
                    "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Max] ), _T )
                )
            )
        )
    )

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 ,

 

You are awesome, thanks a lot for providing the formula.   The Min formula works good , Not to complain but for some reason my Max formula is not behaving good only for the values which are having the "End Date" is empty.

I am trying to troubleshoot , but not getting any good pointers. Do you have any thoughts on this?
I have totally a new different issue on  calculating and get the totals of values  which i will post it in a different new thread. 


Hi @sherifff ,

I am happy to solve the problem for you, did you download my sample, it works fine in my sample by the formula.

vkalyjmsft_0-1639718639016.png

I attach it below, you can download it 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 ,

 

I got the root cause of the issue. On your data set i was able to get the correct results whereas on mine i was not getting the correct results, after analysis i found that the values in the data set, it is having all in incremental from from Period 1 to Period 2 meaning (Start Date - End Date).  Whereas in my data i have the min and max in the mixed format due to which i was getting the wrong values.  
I was able to reproduce the issue with the dataset.  Please refer the below screen shot for the issue details.

 

sherifff_0-1640073021646.png

Could you please take a look and kindly provide formula in fixing the above mentioned issues.

Thanks

Hi @sherifff ,

You described it very clearly, however the logic of the previous formula is based on the fact that max and min in the sample increase with date. If this is not the case, need to rewrite the formula. I may need a little time.

Best Regards,
Community Support Team _ kalyj

Hi @v-yanjiang-msft,

 

Thanks for the consideration. For some of the datasets It is increasing based on that I provided the samples but on the actual data I discovered that i have the scenario which I have explained in my previous post, sorry about that.   Thanks for your help

Hi @sherifff ,

If you want the min and max reutrn the current value in the master table, it should have relationship between the two tables, I create a start date measure in the Transaction table, but it cannot make relationship because of circle dependency was detected, so I copy the start date column in Transaction table and paste it in the data source in Transaction table.

1.In Transaction table create a measure start date.

Start Date = 
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
VAR _T2=
FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Sql_Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[Sql_End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
RETURN
IF(MAXX(FILTER('Master','Master'[Test Name]=MAX('Transaction'[Test Name])),'Master'[Start Date])=BLANK(),
 IF (
        MAX ( 'Transaction'[Lab Test Taken Date] )
            > MAXX (
                FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                'Master'[Sql_Start Date]
            ),
        MAXX (
                FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                'Master'[Sql_Start Date]
            ),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                < MINX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Sql_Start Date]
                ),
             MINX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Sql_Start Date]
                ),
            CALCULATE(MAX('Master'[Sql_Start Date]),_T2)
        )
    ),
    IF (
        MAX ( 'Transaction'[Lab Test Taken Date] )
            > MAXX (
                FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                'Master'[Start Date]
            ),
        MAXX (
                FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                'Master'[Start Date]
            ),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                < MINX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Start Date]
                ),
            MINX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Start Date]
                ),
            CALCULATE(MAX('Master'[Start Date]),_T)
        )
    )
   )

2.In Transaction table create a column.

Column = 'Transaction'[Start Date]

Copy the column and paste it in the data source in Transaction table, get a new column Rela-Column.

vkalyjmsft_0-1640163326685.png

3.Create a measure in Master table.

New start date = IF(HASONEVALUE('Master'[Start Date]),'Master'[Start Date],'Master'[Sql_Start Date])

4.Make relationship between the two tables like this.

vkalyjmsft_1-1640163482086.png

5.Create min and max measure in the Transaction table.

Min2 =
SWITCH (
    MAX ( 'Transaction'[Test Type] ),
    "BP",
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
                    && 'Master'[New start date] = 'Transaction'[Start Date]
            ),
            'Master'[BP_Min]
        ),
    "Cholesterol",
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
                    && 'Master'[New start date] = 'Transaction'[Start Date]
            ),
            'Master'[Cholesterol_Min]
        ),
    "Sugar",
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
                    && 'Master'[New start date] = 'Transaction'[Start Date]
            ),
            'Master'[Sugar_Min]
        )
)
Max2 =
SWITCH (
    MAX ( 'Transaction'[Test Type] ),
    "BP",
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
                    && 'Master'[New start date] = 'Transaction'[Start Date]
            ),
            'Master'[BP_Max]
        ),
    "Cholesterol",
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
                    && 'Master'[New start date] = 'Transaction'[Start Date]
            ),
            'Master'[Cholesterol_Max]
        ),
    "Sugar",
        MAXX (
            FILTER (
                ALL ( 'Master' ),
                'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
                    && 'Master'[New start date] = 'Transaction'[Start Date]
            ),
            'Master'[Sugar_Max]
        )
)

Get the result.

vkalyjmsft_2-1640163632478.png

I attach my sample below for reference.

Best Regards,

Kaly Jiang

 

Hi @v-yanjiang-msft ,

 

Thanks for providing the updated formula.  on my dataset i am getting the error while performing the step #2.  Creating a column in transaction table.   It is throwing error as "A circular depndency was detected".   

Not sure if the Conditional column is the reason for this issue ,  In the transaction table the  "Test Name" is a conditional column.    Any thoughts on this?

 

Hi @sherifff ,

If the Test Name is a calculated column based on Master table, it may cause the problem. But I doubt how the Test Name be calculated, it should be defined by common sense.

Best Regards,

Kaly Jiang

Hi @v-yanjiang-msft,

The Test name is a conditional column (not calculated column) which comes from the source column called "Procedure Code" which is in the same 'Transaction Table' itself.   There are conditions based on which the procedure code will have the test name as "Test100" v/s "Test200".

Please refer the below screen shot.

sherifff_0-1640307986106.png

and then the next step i do is filter it out the blank values and have only the ones with the values . Otherwise the transaction table  will look like below.

sherifff_1-1640308408393.png

 

thats the reason.   I hope i explained the situation.

 

Now, In my dataset today when i tried creating the column called "Column" i tried to use the column data type as "Text" then i was getting the results but not working if i have data type as "date".

Since i have the data as Text column , can i still copy and paste it into data source as  "Rela-Colum"?    If yes,   I would need your help to give me the steps of doing it. 

 

In your pbix i deleted the "Rela-Column" and  i wanted to perform the step, but i don't know how to do it.   I am refering to your part of step #2 "Copy the column and paste it in the data source in Transaction table, get a new column Rela-Column."

 

Could you please help me with the step.

 

Thanks.

 

 

Hi @sherifff ,

The "Column" should be of "Date" type, not "Text", I mean "copy the column and paste it in the data source in Transaction table, get a new column Rela-Column" is just click the Column and select copy, then paste the column in your data source(for example Excel).

vkalyjmsft_0-1640748193982.png

Best Regards,

Kaly Jiang

Hi @v-yanjiang-msft ,

 

I was able to fix the circular data issue and now i am able to get the data in the date format for step # 2,  

As for creating a Rela-Column on the data source, My data source is from another application which is on SQL table.   Could you please guide me as how to handle it here in my case.?

Thanks


Hi @sherifff ,

I haven't done any related operations, I searched this video, please see if it helps you.

https://www.youtube.com/watch?v=1he5oZzG33o

Best Regards,

Kaly Jiang

Hi @v-yanjiang-msft ,

 

Thanks for your reply, I have gone through the link, it is the step where we need to create a new column in the source table.  I have to work with the other app admins and i will update you once i have the column.    They need to create the column with the data what i would be supplying as part of step# 2 , am i right?    Question, Do we have any other alternate options , skipping the creation of column in the source system, curious to know.

 

Thanks

Hi @v-yanjiang-msft,

 

The other application admin from where i get the transaction table said that they cannot change / alter the table at the source level.

 

I am kind of stuck here,  Could you please help me and suggest some possible solution. 

 

Thanks

Hi @sherifff ,

Acctually I can’t think of any other easier way, maybe you can @ other top solution authors to see if they have an easier way. Sorry for not being able to solve your problem.

Best Regards,

Kaly Jiang

Hi @v-yanjiang-msft ,

 

Thanks for all your support, I really appreaciate it.   As you suggested i will check with other solution authors.

 

Hi @amitchandak , 

Could you please take a look at my issue and guide me with possible solution approach.

 

Thanks

Hi @parry2k / @amitchandak ,

 

I am reaching out to you to see if you can help me in resolving my issue. I am kind of stuck with my solution.   Though my requirement is simple, but the way the data is flowing i am kind of stuck and not finding suitable solution.   Could you please help me with my issue?

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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