Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Ajeck
Helper II
Helper II

distinc not equal with sum of monthly production

Hi every one,

 

I need a help,

 

i have the data with duplicate product code, then to annulir this i used a distinc so, the report table is like this : 

Ajeck_0-1685438757645.png

but when I add up, 4,829 + 3,715 + 3,420 + 3,205 is not 15,165.

 

SO i want the report table to be like this : 

Ajeck_1-1685438907902.png

what should I do with Dax to get what i want?

 

Here is the Pbix file 

https://www.dropbox.com/s/nlhqwyy33826lqn/tes%20unit%20distinct%202.pbix?dl=0 

 

Thank you

 

Rgds

Aji

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Ajeck 
Please refer to attached sample file with the proposed solution.

1.png

distinc2 = 
VAR CurrentDate = MIN ( Table1[date] )
VAR CurrentCodes = VALUES ( Table1[kode Product] )
VAR PreviousCodes = 
    CALCULATETABLE ( 
        VALUES ( Table1[kode Product] ),
        Table1[date] < CurrentDate,
        ALLSELECTED ( )
    )
VAR Result =
    COUNTROWS ( EXCEPT ( CurrentCodes, PreviousCodes ) )
RETURN
    Result

Hi Tamerj1,

 

Thankyou for helping me,

Will this solution ( MIN (Table[date])  be problematic when the year period is more than 1 year ?

for example if there are same data in 2022 and 2023 so the report table will be :

Ajeck_0-1685505415943.png

the report table should be :

Ajeck_1-1685505506793.png

here is the file with added year 2022

https://www.dropbox.com/s/4474q23amga6otu/tes%20unit%20distinct%202%282%29.pbix?dl=0

So ......, what should i modify with that sollution?

 

Thank you

 

Rgds

Ajeck

 

 

devanshi
Helper V
Helper V

distincvalue = DISTINCT(distinc1)
VAR totalvalue = CALCULATE(SUMX(tablename,SUM(distinc1),ALL(tablename)))
RETURN
totalvalue

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.