Using Min or First Date with USERELATIONSHIP

DAX DAX Calculations
1

Hi All ,

image

The above table shows Client 1 requested a backorder for Apples on 3 x Different dates.

I want to showcase the count as 1 using use relationship on the created date but only for the first date (i.e-17/4/2020)

My Measures:

Total Count = DISTINCTCOUNT('Data'[Name])

Earliest Count = VAR Products_Ordered =
    SUMMARIZE( 'Data', 'Data'[Status], 'Data'[Name],'Data'[Created Date] )
VAR First_Entry_Products_Ordered =
    ADDCOLUMNS(
        Products_Ordered,
        "Earliest Created Date", MIN( 'Data'[Created Date] )
    )
VAR First_Entry_Products_Ordered_By_Min_Date =
    TREATAS(
        First_Entry_Products_Ordered,
        'Data'[Status],
        'Data'[Name],
        'Data'[Created Date],
        'Dates'[Date]
    )
VAR Result =
    CALCULATE( [Total Count], First_Entry_Products_Ordered_By_Min_Date )
RETURN
    Result

Total by Status = VAR Filt =
    FILTER( 'Data', 'Data'[Status] IN { "Shipped", "Backorder" } )
RETURN
    CALCULATE(
        [Earliest Count],
        Filt,
        USERELATIONSHIP ( 'Dates'[Date], 'Data'[Created Date] )
    )

Output:

image

Expected Output:
Should only show the value for FY20 as the first date is 17/4/2020

I think it has something to do with the date context of my measure but can’t seem to figure out where to resolve it.

I found this post : Calculate the sales amount from earliest date for same customerID with multiple orderdate useful, however still unsure how i can get the min date from the measure to use in userelationship.

Thanks
Sample.pbix (68.9 KB)

2

Hello @adsa,

Thank You for posting your query onto the Forum.

To achieve the results based on the condition that you’ve specified. Below is the measure alongwith the screenshot of the final results provided for the reference -

Total By Status - Harsh = 
CALCULATE(
    VAR _Client_ID = 
    SELECTEDVALUE( Data[Client ID] )

    VAR _First_Order_Date =
    MINX(
        FILTER( ALL( Data ) , 
            Data[Client ID] = _Client_ID ) ,
        Data[Created Date] )

    VAR _Results = 
    CALCULATE( COUNTROWS( Data ) , 
        FILTER( Dates , 
            Dates[Date] = _First_Order_Date ) )

    RETURN
    _Results ,
        USERELATIONSHIP( Dates[Date] , Data[Created Date] ) )

Final Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Min or First Order Date - Harsh.pbix (73.3 KB)

2 Likes
3

Hi @adsa,
This is the measurement that I have found similar to the one you were doing, but after seeing the one made by the expert @Harsh, I see that the latter is more efficient, since it does not use the SUMARIZE function.

Total by Status - JAFP =
VAR Products_Ordered =
SUMMARY(
‘Data’,
‘Data’[Status],
‘Data’[Name],
‘Data’[Created Date]
)
VAR First_Entry_Products_Ordered = ADDCOLUMNS(
Products_Ordered,
“Earliest Created Date”, MIN( ‘Data’[Created Date] )
)
VAR _FilterTable = FILTER(
First_Entry_Products_Ordered,
[Created Date] = [Earliest Created Date] &&
[Status] IN { “Shipped”, “Backorder” }
)
VAR _Result = CALCULATE(
[TotalCount],
_FilterTable,
USERELATIONSHIP( ‘Dates’[Date],‘Data’[Created Date] )
)

RETURN
_Result

Regards,

1 Like
4

@Harsh Thanks for the solution and it does work well, however have run into 1 minor issue.

The measure works well when there are slicer selections. However if there are no selections it come up with blank. What I am trying to do is showcase it on a card visual for just backorder.

I understand that because we are utilizing the ‘Selectedvalue’ function, it expects a value to be passed through in this case the ‘Client ID’ slicer.

Writing this measure does not help as it is still expecting a selectedvalue form the client id slicer .

Backorders =
CALCULATE (
    [Total By Status - Harsh],
    FILTER ( Data, Data[Status] = "Backorder" )
)

image

5

@jafernandezpuga Agreed!

6

Hello @adsa,

Ok, write the below provided measure then -

Total By Status - Harsh 2 = 
CALCULATE(
    VAR _First_Order_Date =
    MINX(
        FILTER( ALL( Data ) , 
            Data[Client ID] = Data[Client ID] ) ,
        Data[Created Date] )

    VAR _Results = 
    CALCULATE( COUNTROWS( Data ) , 
        FILTER( Dates , 
            Dates[Date] = _First_Order_Date ) )

    RETURN
    _Results ,
        USERELATIONSHIP( Dates[Date] , Data[Created Date] ) )

This measure will provide the result even though there’re no selections into the slicer. Below is the screenshot of the final results provided for the reference -

Final Results
Final Results1896×929 85.7 KB

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Min or First Order Date - Harsh v2.pbix (73.7 KB)

7

@Harsh Thanks for that. I’ve tried to implement the the exact same measure context into my live model however I now get a blank value.

On further debugging the issue is around here (See highlighted). It is not getting the current clientID and as a result it takes the minimum date across the entire table.

Intrestingly if i explicitly provide a client id it works for just that client id.

image

8

@adsa - So what do you want to me do in this case.

What’s actually required?

9

Hello @adsa,

In order to remove blanks, I’ve created two conditions inside a measure i.e., one when a slicer selection is made and one when slicer selection is not made.

And in order to create these two conditions, I’ve re-modelled the data. Initially, there was only a Fact table i.e., Data table but now, I’ve created the Dimension tables i.e., Name, Status, Client ID tables. Below is the screenshot of the data model provided for reference -

Data Model
Data Model1642×883 39 KB

Now, created the measure with the conditions inside it. Below is the measure provided for reference -

Total Status - Harsh = 
VAR _Condition_1 = 
CALCULATE(
    VAR _Client_Name = 
    SELECTEDVALUE( Data[Client ID] )

    VAR _First_Order_Date =
    MINX(
        FILTER( ALL( Data ) , 
            Data[Client ID] = _Client_Name ) ,
        Data[Created Date] )

    VAR _Results = 
    CALCULATE( COUNTROWS( Data ) , 
        FILTER( Dates , 
            Dates[Date] = _First_Order_Date ) )

    RETURN
    _Results ,
        USERELATIONSHIP( Dates[Date] , Data[Created Date] ) ,
        FILTER( 'Data', 
            'Data'[Status] IN { "Shipped", "Backorder" } ) )

VAR _Condition_2 = 
CALCULATE(
    VAR _First_Order_Date =
    MINX(
        FILTER( ALL( Data ) , 
            Data[Client ID] = Data[Client ID] ) ,
        Data[Created Date] )

    VAR _Results = 
    CALCULATE( COUNTROWS( Data ) , 
        FILTER( Dates , 
            Dates[Date] = _First_Order_Date ) )

    RETURN
    _Results ,
        USERELATIONSHIP( Dates[Date] , Data[Created Date] ) ,
        FILTER( 'Data', 
            'Data'[Status] IN { "Shipped", "Backorder" } ) )

RETURN
IF( SELECTEDVALUE( 'Client ID'[Client ID] ) , 
    _Condition_1 , 
    _Condition_2 )

So inside a measure, the condition’s states that - “If a slicer selection is made then evaluate the results based on that or else provide the minimum results of all time.”

Below is the screenshot of the final results provided for the reference -

1). Results when slicer selection IS NOT made -

Results when slicer selection is not made
Results when slicer selection is not made758×917 21.4 KB

As said above, when slicer selection is not made, it’ll consider the minimum date from the beginning of the time and provide the results.

2). Results when slicer selection IS made -

Results when slicer selection IS made
Results when slicer selection IS made750×933 16.6 KB

So now, I don’t see the results as “BLANKS” anywhere except where the status is “Processing” or “Created Date is not equal to Minimum Date”.

I’m attaching the PBIX file as well as for the reference.

Thanks and Warm Regards,
Harsh

Min or First Order Date - Harsh v3.pbix (76.8 KB)

10

@Harsh

I have prepared a sample set and applied your measure v2 to showcase the issue

Sample Set.xlsx (18.9 MB)
Sample PBI.pbix (8.8 MB)

I will review v3 of your measure shortly.

11

@harsh Implemented v3 of your measure in the sample data set i recently provided, still not having any luck. I’m looking to obtain the minimum date by customer ID & Status This is the measure i wrote:

Harsh's Measure v3 = 
VAR _Condition_1 = 
CALCULATE(
    VAR _Client_Name = 
    SELECTEDVALUE( 'Sample Data'[Customer ID] )

    VAR _First_Order_Date =
    MINX(
        FILTER( ALL( 'Sample Data' ) , 
            'Sample Data'[Customer ID] = _Client_Name ) ,
        'Sample Data'[Created Date] )

    VAR _Results = 
    CALCULATE( COUNTROWS( Data ) , 
        FILTER( Dates , 
            Dates[Date] = _First_Order_Date ) )

    RETURN
    _Results ,
        USERELATIONSHIP( Dates[Date] , Data[Created Date] )  )

VAR _Condition_2 = 
CALCULATE(
    VAR _First_Order_Date =
    MINX(
        FILTER( ALL( 'Sample Data' ) , 
            'Sample Data'[Customer ID] = 'Sample Data'[Customer ID]
            
             ) ,
        'Sample Data'[Created Date] )

    VAR _Results = 
    CALCULATE( COUNTROWS( Data ) , 
        FILTER( Dates , 
            Dates[Date] = _First_Order_Date ) )

    RETURN
    _Results ,
        USERELATIONSHIP( Dates[Date] , Data[Created Date] ))

RETURN
IF( SELECTEDVALUE( 'Sample Data'[Customer ID] ) , 
    _Condition_1 , 
    _Condition_2 )

Output:

image
image853×253 11.3 KB

13

Hello @adsa,

The query which you’re raising in your later posts and the query which you had raised in your original post does that anywhere match?

Your initial post was about the value to be retrieved in a table and this was the output you were looking for -

image

And it was already achieved.

Later, the requirement changed to a card and now, again your output has changed with the new set of dataset.

If your original query has been resolved than could you please create a new thread with the revised set of requirements mentioned in it alongwith the results that you’re expecting.

Thanks and Warm Regards,
Harsh

14

@Harsh in the original dataset your measure v2 worked when i placed it in a table.

image
image779×352 10.5 KB

I then posted a second dataset and used your v2 measure but can’t get it to work.

1st Dataset: client id > 2nd Dataset: customer id
1st Dataset: Status> 2nd Dataset: status

Just want to understand why the measure doesn’t work in the 2nd dataset?

Thanks

15

Hello @adsa,

So you’re suggesting that “Measure Version 3” is also not working.

The reason why it’s not working is because you’re referencing two different tables inside the measure. See this -

Formula Error
Formula Error1333×1220 96.7 KB

Since you want the results based on a “Sample Data” table then why “Data” table is getting referenced inside this measure? Also in my previous file I had created couple of dimension tables. In your new file, you haven’t created that as well.

I created a new PBIX file based on the new dataset that you’ve provided to see whether my “Measure Version 3” is working or not. And it’s working absolutely fine!!!

Firstly, you’ll be required to convert your flat file into a proper data model. Below is the screenshot of the data model provided for the reference -

Data Model
Data Model1824×959 32 KB

Now, use that “Version 3” measure. Below is the measure again provided for the reference -

Harsh's Measure v3 = 
VAR _Condition_1 = 
CALCULATE(
    VAR _Client_Name = 
    SELECTEDVALUE( Data[Customer ID] )

    VAR _First_Order_Date =
    MINX(
        FILTER( ALL( Data ) , 
            Data[Customer ID] = _Client_Name ) ,
        Data[Created Date] )

    VAR _Results = 
    CALCULATE( COUNTROWS( Data ) , 
        FILTER( Dates , 
            Dates[Date] = _First_Order_Date ) )

    RETURN
    _Results ,
        USERELATIONSHIP( Dates[Date] , Data[Created Date] )  )

VAR _Condition_2 = 
CALCULATE(
    VAR _First_Order_Date =
    MINX(
        FILTER( ALL( Data ) , 
            Data[Customer ID] = Data[Customer ID] ) ,
        Data[Created Date] )

    VAR _Results = 
    CALCULATE( COUNTROWS( Data ) , 
        FILTER( Dates , 
            Dates[Date] = _First_Order_Date ) )

    RETURN
    _Results ,
        USERELATIONSHIP( Dates[Date] , Data[Created Date] ))

RETURN
IF( SELECTEDVALUE( 'Customer ID'[Customer ID] ) , 
    _Condition_1 , 
    _Condition_2 )

And now, if you see, it’s evaluating the results when slicer selection is made and even when slicer selection is not made. It’s even evaluating the results inside the cards as well so I’m not getting any results as Blanks from my side. Below is the screenshot provided for the reference -

1). Results when slicer selection IS NOT made -

Results when slicer selection is not made
Results when slicer selection is not made924×437 14.1 KB

2). Results when slicer selection IS made -

Results when slicer selection IS made
Results when slicer selection IS made908×412 14.1 KB

I’m also attaching the new working PBIX file for the reference based on the new dataset which was provided.

Thanks and Warm Regards,
Harsh

Min Or First Order Date Based On New Data - Harsh.pbix (9.7 MB)

16

@harsh It’s been a long day and thank you for your patience. I do have another question that will branch off from this but i will create a new thread.

Once again thanks for your help.