Sum distinct values for first occurance in Power BI with Filter

0
votes

In Power BI I have some duplicate entries in my data that only have 1 column that is different, this is a "details" column.

enter image description here

Basically, when I sum up my Value column on a Power BI card, I want it to filter IsActive = 1 and sum for each unique name, so in this case:

Total= 10 + 7

Is there any way I can filter this with a DAX formula?

3
powerbidax
Hey @David Yuan, All the below answers provided are potential solutions for your request. Were you able to reciprocate them and get the solution which you expected? – Nandan

3 Answers

1
votes

Assuming your table can also have a row with the same value of another row but a different name, and a row where Details column doesn't always include "Feature 1"

Name    Values  Details IsActive
Item 1  10  Feature 1   1
Item 1  10  Feature 2   1
Item 2  15  Feature 1   0
Item 3  7   Feature 1   1
Item 3  7   Feature 2   1
Item 3  7   Feature 3   1
Item 4  10  Feature 1   1
Item 5  10  Feature A   1

then we should use the Name column an write something like follows

Total = 
CALCULATE(
    SUMX( SUMMARIZE( T, T[Name], T[Values] ), T[Values] ),
    T[IsActive] = 1
)
0
votes

You can create a calculated column wherein you rank the rows based on the occurrence via M-query as provided in the below link :

https://community.powerbi.com/t5/Desktop/How-to-add-Row-number-over-partition-by-Customer-DAX-or-M-query/td-p/566949

Once the calculated column is done, you can achieve your result based on the below measure : sum(value) where IsActive=1 and calculatedColumn=1 via on Filter DAX

0
votes

It doesn't appear that the first occurrence is relevant, so you can just write a measure to sum distinct values.

SUMX (
    CALCULATETABLE (
        VALUES ( Table1[Value] ),
        Table1[IsActive] = 1
    ),
    Table1[Value]
)