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.
Hi Team,
I have data like below (from column 1 to 3) and I need to create two calculated column for 4 and column 5
PBIX file attached for sample data and two require column
Thanks in Advance
Solved! Go to Solution.
Hi @PijushRoy ,
Redo the calculation to:
CUST SERIES =
VAR MAXINVOICE =
IF ( Data[Hown Many Invoices] = 1, Data[Index],Data[Index]+ Data[Hown Many Invoices]-1 )
RETURN
IF (
Data[Hown Many Invoices] <> BLANK (),
Data[Invoice_ID] & "-"
& MAXX ( FILTER ( ALL ( Data ), Data[Index] = MAXINVOICE ), Data[Invoice_ID] )
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @PijushRoy ,
Try the following two codes:
Hown Many Invoices =
VAR temptable =
TOPN (
1,
FILTER (
Data,
Data[Unique_Cust] <> 1
&& Data[Index] > EARLIER ( Data[Index] )
),
Data[Index], ASC
)
VAR Row_Number =
MINX ( temptable, Data[Index] ) - Data[Index]
VAR TotalRows =
MAXX ( Data, Data[Index] )
RETURN
IF (
Data[Unique_Cust] <> 1,
IF ( Row_Number > 0, Row_Number, TotalRows - Data[Index] + 1 )
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Small mismatch
It is taking Index 4 rows Invoice number inplace of Index 25 Invoice no
Hi @PijushRoy ,
Redo the calculation to:
CUST SERIES =
VAR MAXINVOICE =
IF ( Data[Hown Many Invoices] = 1, Data[Index],Data[Index]+ Data[Hown Many Invoices]-1 )
RETURN
IF (
Data[Hown Many Invoices] <> BLANK (),
Data[Invoice_ID] & "-"
& MAXX ( FILTER ( ALL ( Data ), Data[Index] = MAXINVOICE ), Data[Invoice_ID] )
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@PijushRoy you haven't provided more information
i haven't done all of it but is this what you are looking for? please confirm - you haven't give enough information, you havent provided an expected solultion. If you need help you do need to provide a clear set of what is expected.
Proud to be a Super User!
@vanessafvg
Can you please share the pbix, when I applied calcualtion, I am not getting same data as you mentioned in image.
it changed thats probably why. Its still not perfect however but i am unsure of what you want so please clarify with the exact results.
Get UniqueCust =
VAR selinvoiceid =
SELECTEDVALUE ( Data[Invoice_ID] )
VAR mininvoiceid =
CALCULATE ( MIN ( Data[Invoice_ID] ) )
VAR maxindex =
CALCULATE (
MIN ( Data[Index] ) - 1,
ALL ( data ),
Data[Invoice_ID] > mininvoiceid
&& Data[Unique_Cust] > 1
)
VAR minindex =
CALCULATE (
MAX ( Data[Index] ),
ALL ( data ),
Data[Index] < maxindex
&& Data[Unique_Cust] > 1
)
VAR firstinvoiceid =
CALCULATE ( MIN ( Data[Invoice_ID] ), ALL ( Data ), Data[Index] = minindex )
VAR maxinvoiceid =
CALCULATE ( MAX ( Data[Invoice_ID] ), ALL ( data ), Data[Index] = maxindex )
VAR result =
IF ( selinvoiceid = maxinvoiceid, selinvoiceid, firstinvoiceid ) & "-"
& IF ( ISBLANK ( maxinvoiceid ), mininvoiceid, maxinvoiceid )
RETURN
result
Proud to be a Super User!
Hi,
In a simple MS Excel file, please show the expected result with formulas/comments.
its not perfect but its a something to work with i needs more tweaking
Get UniqueCust =
VAR mininvoiceid =
MIN ( Data[Invoice_ID] )
VAR maxindex =
IF (
SUM ( Data[Index] ) > 1,
CALCULATE (
MIN ( Data[Index] ) - 1,
ALL ( data ),
Data[Invoice_ID] > mininvoiceid
&& Data[Unique_Cust] > 1
),
BLANK ()
)
VAR maxinvoiceid =
CALCULATE ( MAX ( Data[Invoice_ID] ), ALL ( data ), Data[Index] = maxindex )
RETURN
mininvoiceid & "-"
& IF ( ISBLANK ( maxinvoiceid ), mininvoiceid, maxinvoiceid )
Proud to be a Super User!
@vanessafvg
Thanks for your help, however it is not working and not shoiwng correct result.
as i said it needs tweaking. however agree providing more information might be useful as the in expected result for each row.
Proud to be a Super User!
Hi,
Any help please
Please refer to modified pbix attached.
Edit:
Just realized I didn't follow through with all your requirements. Hoping though you can see the pattern and make the necessary modifications.
Trust you will be able to follow the logic.
Any questions, please ask.
Best Regards,
Hi @rsbin
Thanks for your help, but it is not working
Series ending value is not matching
Invoice count number is not correct for all
I thought the logic of capturing the first 3 characters would work, but unfortunately
this appears not to be the case.
Looks like another approach or method will be required.
Might be worthwhile closing this thread and start a new one.
Revised file attached. Gets a little closer but still instances where logic is incomplete
Hi @rsbin
I need to solve this urgently, I am also trying. Can you please try and let me kwon, It will great help for me.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |