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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tsarun1992
Helper I
Helper I

Data Modelling

My data would have 2 columns names "Posting Date" and "Name" as listed below.

The third column should be generated through a formula with the below condition.

 

For every name, for every date, a value of 8.5 should be shown only once in the third column.

 

tsarun1992_0-1671088628020.png


Is there an formula to generate this in Excel and DAX in PowerBI?

4 REPLIES 4
FreemanZ
Super User
Super User

Hi @tsarun1992 

 

1) add an index column in PQ:

https://learn.microsoft.com/en-us/power-query/add-index-column

If you can provide more info about your data, it also possible to add an index column with DAX

 

2) create a column with this:

Column = IF(MOD([Index],2)=1,8.5)
 
i tried and it worked like this:
FreemanZ_0-1671090881101.png

Hey hi,

 

Thanks for the response.

My data is very simple but large. It will have "n" number of Date entries for a single day as the NAMES are 100+.

Also one person can have multiple entries for a day too. 

For eg, If there are 100 resources & 2 entries per resource per day, there are 200 entries per day and for a year there will be 200*365 = Approx 73000 entries.

But the value 8.5, I should get it only for one resource per day, so 100 entries and 365 days do 100*365 = 36500 entries as "8.5".

Hope I'm clear in my explanation. 

The sample needs to be representative enough. Try to enrich your sample by 

1) including a unique column you already have, otherwise an index column is suggested.

2) increasing the complexity, showing how "one person can have multiple entries for a day"

 

p.s. 73000 is not a big number. 

Thanks let me try.

 

73000 was just an example. The actual counts to 1 Million which still might not be a big number to you 😄

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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