cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
PijushRoy
Solution Sage
Solution Sage

Need DAX help to generate number series based on other col value

Hi Team,

I have data like below (sample)

 

Index Vehicle No Code Company Height Rate DuplicateCheck No Series
0 025010 S342 Com A ALL 48 54.72 25010 025010-025010
1 025132 S332 Com A ALL 48 43.92 25132 025132-025132
2 025294 S332 Com A ALL 48 43.92    
3 025389 S332 Com A ALL 48 43.92    
4 025392 S332 Com A ALL 48 43.92    
5 025394 S332 Com A ALL 48 43.92    
6 027019 S332 Com B ALL 48 44.55 27019 027019-027019
7 027027 S332 Com B ALL 48 44.55    
8 027034 S332 Com B ALL 48 44.55    
9 027081 S332 Com B ALL 48 44.55    
10 027085 S332 Com B ALL 48 44.55    
11 027086 S335 Com B ALL 49 45 27086 027086-027086
12 027094 S335 Com B ALL 49 45    
13 027127 S335 Com B ALL 49 45    
14 027151 S335 Com B ALL 49 45    
15 027267 S335 Com B ALL 49 45    
16 027280 S335 Com B ALL 49 45    
17 027312 S335 Com B ALL 49 45    

 

If any value change in col Code, Company, Height, Rate then new No Series should generated. But end of the No Series, should be End Vehicle No of that series,.
Now it is wrongly generated in Calcu;ated column (RED cell), require result in GREEN cell

001.JPG

Any idea to solve this if calcuted column or measure.

 

Thanks in Advance

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @PijushRoy ;

You could create a column by dax:

Column = 
IF([Index]=
CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])),[Vehicle No]&"-"&
CALCULATE(MAX('Table'[Vehicle No]),FILTER('Table',[Code]=EARLIER('Table'[Code])&&[Company]=EARLIER('Table'[Company])
        &&[Index]=CALCULATE(MAX('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])))))

or a measure

Measure = 
IF(MAX('Table'[Index])=CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])),MAX('Table'[Vehicle No])&"-"&
CALCULATE(MAX('Table'[Vehicle No]),FILTER(ALL('Table'),[Code]=MAX('Table'[Code])&&[Company]=MAX('Table'[Company])&&[Index]=CALCULATE(MAX('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])))))

The final show:

vyalanwumsft_0-1667801144894.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-yalanwu-msft
Community Support
Community Support

Hi, @PijushRoy ;

Try it.

Column = 
IF([Index]=
CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])),[Vehicle No]&"-"&
CALCULATE(MAX('Table'[Vehicle No]),FILTER('Table',[Code]=EARLIER('Table'[Code])&&[Company]=EARLIER('Table'[Company])
        &&[Index]=CALCULATE(MAX('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])))),FORMAT( CALCULATE(COUNT('Table'[Vehicle No]),ALLEXCEPT('Table','Table'[Code],'Table'[Company]))-1,"0"))

The final show:

vyalanwumsft_0-1667897548858.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yalanwu-msft , 
I am expecting, I have created new ticket for this
Can you please look 
https://community.powerbi.com/t5/Desktop/Counts-no-of-rows-in-calculated-column-with-blank-rows-by-D...

v-yalanwu-msft
Community Support
Community Support

Hi, @PijushRoy ;

You could create a column by dax:

Column = 
IF([Index]=
CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])),[Vehicle No]&"-"&
CALCULATE(MAX('Table'[Vehicle No]),FILTER('Table',[Code]=EARLIER('Table'[Code])&&[Company]=EARLIER('Table'[Company])
        &&[Index]=CALCULATE(MAX('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])))))

or a measure

Measure = 
IF(MAX('Table'[Index])=CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])),MAX('Table'[Vehicle No])&"-"&
CALCULATE(MAX('Table'[Vehicle No]),FILTER(ALL('Table'),[Code]=MAX('Table'[Code])&&[Company]=MAX('Table'[Company])&&[Index]=CALCULATE(MAX('Table'[Index]),ALLEXCEPT('Table','Table'[Code],'Table'[Company])))))

The final show:

vyalanwumsft_0-1667801144894.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yalanwu-msft 
Thanks for your reply and post already solved.
I am using calculated column. Can you please help me get blank cell as pervious value
like 
25132-25394 for below 4 blank cell
or 
Count of 25132-25394 i.e. 5


hnguy71
Memorable Member
Memorable Member

Hi @PijushRoy ,

 

Try this:

FindCorrectDupes = 

VAR _GroupRecords = CALCULATETABLE(ALLSELECTED('YOUR_TABLE'), 'YOUR_TABLE'[Code] = EARLIER([Code]) && 'YOUR_TABLE'[Company] = EARLIER([Company]) && 'YOUR_TABLE'[Height] = EARLIER([Height]) && 'YOUR_TABLE'[Rate] = EARLIER([Rate]))
VAR _MaxID = CALCULATE(MAX('YOUR_TABLE'[Index]), FILTER('YOUR_TABLE', 'YOUR_TABLE'[Code] = EARLIER([Code]) && 'YOUR_TABLE'[Company] = EARLIER([Company]) && 'YOUR_TABLE'[Height] = EARLIER([Height]) && 'YOUR_TABLE'[Rate] = EARLIER([Rate])))

VAR _Result = IF( 
    LEN([No Series]) > 0 && COUNTROWS(_GroupRecords) > 1,
    VAR _CurrVehicle = [Vehicle No]
    VAR _MaxVehicle = LOOKUPVALUE('YOUR_TABLE'[Vehicle No], [Index], _MaxID)
    RETURN
    _CurrVehicle & "-" & _MaxVehicle
)

RETURN

_Result

 

 

hnguy71_1-1667566250851.png

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Hi @hnguy71 
Thanks for your reply.
Problem I faced the first row I got blank and trying to fix this, where got another solution from @v-yalanwu-msft and works perfectly after makes some changes.

Thanks again for your prompt and useful DAX

PijushRoy
Solution Sage
Solution Sage

Hi,
For solution, it is ok to replace the blank cell with any value in Duplicate Check col

001.JPG

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors