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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KarinaQuino
New Member

Power BI DAX occurence per part number

I'm working with a dataset in Power BI that includes a table called 'ECN_AFFECTED_ITEMS_V'. This table has columns such as 'Part Number' and 'Release Date'. Each row represents an incident related to a specific part number, and incidents can occur on different dates.

I need to create a DAX formula to analyze incidents within a 6-month period for each part number. Specifically, I want to determine if any part number has multiple incidents that occurred within 6 months of each other and count those occurrences. Additionally, I need to check if any consecutive incidents for each part number meet the condition of being within 6 months of each other. How can I achieve this in Power BI?

 

As seen in the image, that part number has 8 revisions or occurrences. I want to analyze the distance between each date to check if it is <=6 months. If one meets this criterion, mark it as 1; otherwise, mark it as 0. This should be evaluated between each of the dates.

KarinaQuino_0-1713820235953.png

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

Hi,

@Ashish_Mathur , thanks for concern about the problem, and i want to offers some information for user to refer to.

hello @KarinaQuino , based on your description, you can refer to the following solution.

Sample data 

vxinruzhumsft_1-1713855847484.png

1.You can create a rank ciolumn in table

Rank = RANKX(FILTER('Table',[Part Numner]=EARLIER('Table'[Part Numner])),[Release Date],,ASC,Dense)

2.Then create a measure

MEASURE =
VAR _add =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "flag",
            VAR a =
                CALCULATE (
                    MIN ( 'Table'[Release Date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Part Numner] ),
                    'Table'[Rank]
                        = EARLIER ( 'Table'[Rank] ) - 1
                )
            VAR b =
                DATEDIFF ( a, [Release Date], DAY )
            RETURN
                IF ( b <= 180 && b > 0, 1, 0 )
    )
RETURN
    IF (
        SUMX (
            FILTER ( _add, [Part Numner] IN VALUES ( 'Table'[Part Numner] ) ),
            [flag]
        ) > 0,
        1,
        0
    )

Output

vxinruzhumsft_2-1713855931096.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

Hi,

@Ashish_Mathur , thanks for concern about the problem, and i want to offers some information for user to refer to.

hello @KarinaQuino , based on your description, you can refer to the following solution.

Sample data 

vxinruzhumsft_1-1713855847484.png

1.You can create a rank ciolumn in table

Rank = RANKX(FILTER('Table',[Part Numner]=EARLIER('Table'[Part Numner])),[Release Date],,ASC,Dense)

2.Then create a measure

MEASURE =
VAR _add =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "flag",
            VAR a =
                CALCULATE (
                    MIN ( 'Table'[Release Date] ),
                    ALLEXCEPT ( 'Table', 'Table'[Part Numner] ),
                    'Table'[Rank]
                        = EARLIER ( 'Table'[Rank] ) - 1
                )
            VAR b =
                DATEDIFF ( a, [Release Date], DAY )
            RETURN
                IF ( b <= 180 && b > 0, 1, 0 )
    )
RETURN
    IF (
        SUMX (
            FILTER ( _add, [Part Numner] IN VALUES ( 'Table'[Part Numner] ) ),
            [flag]
        ) > 0,
        1,
        0
    )

Output

vxinruzhumsft_2-1713855931096.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thank so much!!

This was exactly what I needed, the truth is I had been trying different ways for 3 days, and this is because I don't have much experience in DAX, but this was perfect! ✌️

Ashish_Mathur
Super User
Super User

Hi,

Share some data to work with and show the expected result very clearly.  Share data in a format that can be pasted in an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.