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
ofeklevy
Frequent Visitor

Getting minimum value out of a calculated table column

I have:

  1. a table with user names

  2. a table indicating actions with columns for user name, action time, action name. Named events unique_events

I started collecting data on January. I want to have a column in my table of user names which indicates how long it has been since a user first used my application and the first of January.

So if a user first logged in in January, the value of the row with that user's name will be 0. If one logged in on March it will be 2.

I tried:

 

Column = DATEDIFF(01-01-2016, MIN(SELECTCOLUMNS(FILTER('events unique_events','events unique_events'[User Name] = Users[User Name]),"DatedTime", [DatedTime])),MONTH)

 

which returns an error saying the Min function needs a column reference.

I also tried the same with FirstDate instead of MIN which returned an error saying FirstDate can't be used with summarize functions.

 

I also tried this:

In my events table I added a calculated column for the time difference between the action time and January, in months; which works ok. Its named TimeDiff

 

I then tried to make a calculated formula in the users table with this:

Column = min(SELECTCOLUMNS(CALCULATETABLE('events unique_events, 'events unique_events[User Name]=Users[User Name]),"T1", [TimeDiff]))

which results in an error, again, "min function only takes a column reference.."

 

Any other ideas on how to achieve this, or fix what I tried?

1 ACCEPTED SOLUTION

@ofeklevy

 

In this scenario, if you [DatedTime] is a column, you can directly create a calculated column in User table to get the Min Date on User level.

 

7.PNG

 

If the [DatedTime] is a measure, you have to create a calculated table to SUMMARIZE() it into a column so that you can use MIN() function.

 

Regards,

 

View solution in original post

3 REPLIES 3
Phil_Seamark
Employee
Employee

Hi @ofeklevy

 

Please try adding this calculated column to your event table

 

Column = 
VAR MinDatePerUser = 
    CALCULATE(
        MIN('events unique_events'[DatedTime]),
        FILTER(
            ALL('events unique_events'),
            'events unique_events'[User Name]=EARLIER('events unique_events'[User Name])
            )
            )
RETURN    DATEDIFF(DATE(2016,1,1),MinDatePerUser,MONTH)       

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hey @Phil_Seamark

Thank you for the answer! I will try it when I have access to my network; however, I would like the indicator of how native a user is to be appear in my Users table (mostly for the sake of performance, as that table is much much smaller than the events table).

Do you know how to create that calculation there instead?

Thanks!

@ofeklevy

 

In this scenario, if you [DatedTime] is a column, you can directly create a calculated column in User table to get the Min Date on User level.

 

7.PNG

 

If the [DatedTime] is a measure, you have to create a calculated table to SUMMARIZE() it into a column so that you can use MIN() function.

 

Regards,

 

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.