Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I have a problem with Power BI
I use a measure since long time that works with data like this
ID Value Date
1 1 1/01
1 -1 1/01
1 1 2/01
2 1 3/01
I can make a Cumulative flow diagram with this :
CALCULATE(
SUM(Table[Value]),
ALL(DATE TABLE),
FILTER(ALLSELECTED, Table[Date]), Table[Date] <= MAX(DATE TABLE[Date])
But now i add values in my data and some ID have Parent
like this
1 1 1/01
1 -1 1/01
1 1 2/01
2 1 3/01 Parent1
2 1 3/01 Parent2
I want the same measure that before but that ignore duplicate with Parents ....
Is this possible ?
Thank you in advance ...
Hello @francisthe63,
Can you please try the following:
Cumulative Flow No Duplicates =
VAR UniqueIDDate =
SUMMARIZE(
Table,
Table[ID],
Table[Date],
"UniqueValue", SUM(Table[Value])
)
RETURN
CALCULATE(
SUMX(
UniqueIDDate,
[UniqueValue]
),
FILTER(
ALLSELECTED('DATE TABLE'),
'DATE TABLE'[Date] <= MAX('DATE TABLE'[Date])
)
)
Hope this helps!
I calculated in Table View and seen that this function of summarize works in a way :
UniqueINDEX = SUMMARIZE(Table,Table[Index],"Value",MAX(Table[Value])) => The max value is the value (because the sum make an addition of my values and i don't want that, i want to keep the value, like remove duplicate)
So i don't understand why :
SUMX(UniqueIndex,UniqueValue),ALL (DATE TABLE),FILTER(ALL SELECTED(Table[Date] <= Max(DATE TABLE[Date]))
Don't work ? In my visual it don't make a cumulative chart like my original measure when i had only one index ...
Hello,
It don't works ... I try to simplifly the data.
=> Before get the parents. I register each rows of my original table in an index table
Exemple :
ID Date Value Index
1 1/01 1 1
1 2/01 -1 2
1 2/01 1 3
2 2/01 1 4
2 2/01 -1 5
When join the parents, the value Index is duplicated (exemple, 2 have 2 parents so there is :
ID Date Value Index
1 1/01 1 1
1 2/01 -1 2
1 2/01 1 3
2 2/01 1 4 Parent 1
2 2/01 1 4 Parent 2
2 2/01 -1 5 Parent 1
2 2/01 -1 5 Parent 2
So the problem is to have this mesure that works in original table :
CALCULATE(
SUM(Table[Value]),
ALL(DATE TABLE),
FILTER(ALLSELECTED(Table[Date]), Table[Date] <= MAX(DATE TABLE[Date])
But with unique index in the new table.
(DATE TABLE is the Table of date of related to the column Date of the table)
To summarize, i think that the only difference with my original measure is that i use the Index value and I want to have the original measure but with removing duplicate index because there are the same rows.
Hi,
Show clearly the exact result which you are expecting.
Hello,
I have my table with :
Index as unique value, Date, State, Value (only -1 or 1)
I made a cumulative chart like this, with my measure :
But when i am assessing parents, value Index is duplicated
I want the same graph than before but with not distinct index.
With the same value (in my example 1507), because with my formula, when Index is duplicated the value at the end of the chart is higher than 1507 because it sum duplicates...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |