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,
Given code and output below, id like to exclude multiple values based on date and name.
IF I have more than one row of same Date&Name id like to output calculation (330) only in one row.
So basically output below in last column would change to 330, 0, 0, 508, 0, 0.
Is there a way to do that? >> I know measure would solve that, but i need to include these values by day in graph so there is a need to do that by calculated col.
Thanks in advance!
Solved! Go to Solution.
Hi @hejszyszky ,
Since I do not have your specific data, I have created a template data based on what you have provided so far:
Then use the below dax to create a new column:
final =
VAR index11 =
RANKX (
FILTER ( 'Summarized Raport', 'Summarized Raport'[Data] = EARLIER ( 'Summarized Raport'[Data] ) ),
'Summarized Raport'[Zmiana(A,B,C,D)],
,
ASC
)
RETURN
IF (
index11 = 1,
SUMX (
FILTER ( 'Summarized Raport', 'Summarized Raport'[Data] = EARLIER ( 'Summarized Raport'[Data] ) ),
'Summarized Raport'[value]
),
0
)
Final output:
You can adapt my code to your specific environment and needs.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @hejszyszky ,
Since I do not have your specific data, I have created a template data based on what you have provided so far:
Then use the below dax to create a new column:
final =
VAR index11 =
RANKX (
FILTER ( 'Summarized Raport', 'Summarized Raport'[Data] = EARLIER ( 'Summarized Raport'[Data] ) ),
'Summarized Raport'[Zmiana(A,B,C,D)],
,
ASC
)
RETURN
IF (
index11 = 1,
SUMX (
FILTER ( 'Summarized Raport', 'Summarized Raport'[Data] = EARLIER ( 'Summarized Raport'[Data] ) ),
'Summarized Raport'[value]
),
0
)
Final output:
You can adapt my code to your specific environment and needs.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi, @hejszyszky
I believe I get your point, here's the picture and code:
DuplicityCheck =
var currentDate = 'Table'[Date]
var currentName = 'Table'[Name]
var currentIndex = 'Table'[Index]
var duplicityvalues = filter('Table', currentDate = 'Table'[Date] && currentName = 'Table'[Name])
var duplicity = COUNTROWS(duplicityvalues)
var LowestDuplicity = MINX(duplicityvalues, 'Table'[Index])
var _removeDuplicate = SWITCH(TRUE(),
LowestDuplicity = currentIndex && duplicity >=2 , "330",
duplicity = 1, "Your expression",
"0")
return _removeDuplicate
Please note that "your expression" represents an expression that you would normally use if everything is correct.
Hi, @vojtechsima
Thanks for your fast reply!
I am facing some issues, i believe its with Index column. In your scenario,Index column, is it just a row number?
Hi, @hejszyszky
It's just a simple Index column generated by Power Query:
However, it really helps with your issue.
Well thats unlucky, cuz my table is an dax expression 😐
@hejszyszky ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.
Hey @amitchandak ,
Thanks for quick response.
Output of measure above is ;
Date | Name | Niezaraportowany czasC |
1/3/2021 | LSN01 | 330 |
1/3/2021 | LSN01 | 330 |
1/3/2021 | LSN01 | 330 |
1/4/2021 | LSN01 | 508 |
1/4/2021 | LSN01 | 508 |
1/4/2021 | LSN01 | 508 |
My desired output would be:
Date | Name | Niezaraportowany czasC |
1/3/2021 | LSN01 | 330 |
1/3/2021 | LSN01 | 0 |
1/3/2021 | LSN01 | 0 |
1/4/2021 | LSN01 | 508 |
1/4/2021 | LSN01 | 0 |
1/4/2021 | LSN01 | 0 |
Zeroes in desired output are because there is a repetition in one date and name
In conclusion, if in any same date and same name there is more than one row, include only one value.
Am i clear here 🙂 ?
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 |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |