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

Create new column checking on other columns data

Hello Community!

I have a Table with the first 2 columns of the following example (ID and Time_1), and I need to add a third column (Time_2), in order to get something like this:

 

IDTime_1Time_2
13010
13010
13010
25050
34020
34020
48020
48020
48020
48020

 

Taking ID = 15 as example, Time_2 should be calculated as 30/3 = 10 (30 is the value of Time_1 and 3 is how many times Order 15 is repeated).

 

I’d be grateful if I could get some help. Thanks in advance

1 ACCEPTED SOLUTION

@aceremin - Try this:

Time_2 = 
    VAR __Time_1 = [Time_1]
    VAR __Num = COUNTROWS(FILTER('Table (18)',[ID]=EARLIER([ID])))
RETURN
    __Time_1/__Num

PBIX is attached below sig. Table (18). 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
nandic
Memorable Member
Memorable Member

@aceremin ,

Try this formula:

Time_2 =
var _Id_Amount = CALCULATE(MIN('Table'[Time_1]),'Table'[ID]=EARLIER('Table'[ID]))
var _Id_Count = CALCULATE(COUNTROWS('Table'),'Table'[ID]=EARLIER('Table'[ID]))
RETURN
DIVIDE(_Id_Amount,_Id_Count)
 
20200902 divide.PNG

Hi @nandic 

Thanks for your help.

 

But I am not getting the result I was expecting.

The column Time_2 shows me the Time_1 value.

Looks like _Id_Count is not counting the number of times the ID appears in the column.

I tried creating the column _Id_Count separatelly, and I get 1 as a result for each row.

In my example I should get something like:

aceremin_0-1599140459220.png

I hope this is clear. Do you know what I should do?

Thanks again!

 

 

@aceremin - Try this:

Time_2 = 
    VAR __Time_1 = [Time_1]
    VAR __Num = COUNTROWS(FILTER('Table (18)',[ID]=EARLIER([ID])))
RETURN
    __Time_1/__Num

PBIX is attached below sig. Table (18). 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.