How to show the days in a row an item has appeared

[DTHOMS1984]

HI,

I have a set of data that shows all data exceptions each day for the past year. I am trying to get Power Bi to show me something like the below in excel 

exception_name	Instrument_id	price_date	price_point	Days in a row appeared as an exception
Primary price source not used	70011646	23/10/2023 00:00	10AM	1
Primary price source not used	70011646	19/10/2023 00:00	10AM	1
Primary price source not used	70011646	03/10/2023 00:00	10AM	1
Primary price source not used	70011646	29/09/2023 00:00	10AM	1
Primary price source not used	70011646	25/09/2023 00:00	10AM	1
Primary price source not used	70011646	20/09/2023 00:00	10AM	2
Primary price source not used	70011646	19/09/2023 00:00	10AM	1
Primary price source not used	70011646	13/09/2023 00:00	10AM	1
Primary price source not used	70011646	25/08/2023 00:00	10AM	1
Primary price source not used	70011646	14/08/2023 00:00	10AM	1
Primary price source not used	70011646	08/08/2023 00:00	10AM	1
Primary price source not used	70011646	01/08/2023 00:00	10AM	1
Primary price source not used	70011646	31/07/2023 00:00	10AM	2
Primary price source not used	70011646	28/07/2023 00:00	10AM	1
Primary price source not used	70011646	12/07/2023 00:00	10AM	1
Primary price source not used	70011646	30/06/2023 00:00	10AM	2
Primary price source not used	70011646	29/06/2023 00:00	10AM	1
Primary price source not used	70011646	23/06/2023 00:00	10AM	1
Primary price source not used	70011646	14/06/2023 00:00	10AM	2
Primary price source not used	70011646	13/06/2023 00:00	10AM	1
Primary price source not used	70011646	31/05/2023 00:00	10AM	1

I tried a group by but it keeps showing me the days between. 

Does anyone have any ideas ?

Thanks

[Ahmedx · Accepted solution]

and pls try this measure

Measure 2 = 
VAR _t1 = MAX('Table'[Instrument_id])
VAR _t2 = FILTER(ALL('Table'),'Table'[Instrument_id]=_t1)
VAR _Rx = RANKX(_t2, CALCULATE(MAX('Table'[price_date])),,ASC,Dense)-1
VAR _Result =
    CALCULATE(
        MAX( 'Table'[price_date] ),
        FILTER(
            ALL( 'Table' ),
           RANKX(_t2, CALCULATE(MAX('Table'[price_date])),,ASC,Dense)= _Rx))
RETURN
    IF(
        MAX( 'Table'[price_date] ) - _Result = 1,
        ( RANKX(_t2, CALCULATE(MAX('Table'[price_date])),,ASC,Dense)-  _rx  )+ 1,
        1
    )

[DTHOMS1984]

That worked thanks very much, just out of interest see if there was ones with a date with up to 5 days in a row how would I change the dax to show the possible variables from 1,2,3,4 or 5 days in a row ?

[Ahmedx · Accepted solution]

pls try

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[DTHOMS1984]

Thanks @Ahmedx Should this work for multiple IDS see below ?

[Ahmedx · Accepted solution]

and pls try this measure

Measure 2 = 
VAR _t1 = MAX('Table'[Instrument_id])
VAR _t2 = FILTER(ALL('Table'),'Table'[Instrument_id]=_t1)
VAR _Rx = RANKX(_t2, CALCULATE(MAX('Table'[price_date])),,ASC,Dense)-1
VAR _Result =
    CALCULATE(
        MAX( 'Table'[price_date] ),
        FILTER(
            ALL( 'Table' ),
           RANKX(_t2, CALCULATE(MAX('Table'[price_date])),,ASC,Dense)= _Rx))
RETURN
    IF(
        MAX( 'Table'[price_date] ) - _Result = 1,
        ( RANKX(_t2, CALCULATE(MAX('Table'[price_date])),,ASC,Dense)-  _rx  )+ 1,
        1
    )

[Ahmedx]

yes, you check!

Measure = VAR _rx =
    RANK(DENSE, SUMMARIZE(ALLSELECTED( 'Table' ),'Table'[exception_name],'Table'[Instrument_id],'Table'[price_date]), ORDERBY( 'Table'[price_date] ), , PARTITIONBY( 'Table'[Instrument_id] ) )-1
VAR _Result =
    CALCULATE(
        MAX( 'Table'[price_date] ),
        FILTER(
            ALL( 'Table' ),
            RANK(DENSE, SUMMARIZE(ALLSELECTED( 'Table' ),'Table'[exception_name],'Table'[Instrument_id],'Table'[price_date]), ORDERBY( 'Table'[price_date] ), , PARTITIONBY( 'Table'[Instrument_id] ) )= _rx))
RETURN
    IF(
        MAX( 'Table'[price_date] ) - _Result = 1,
        ( RANK(DENSE, SUMMARIZE(ALLSELECTED( 'Table' ),'Table'[exception_name],'Table'[Instrument_id],'Table'[price_date]), ORDERBY( 'Table'[price_date] ), , PARTITIONBY( 'Table'[Instrument_id] ) )- ( _rx ) )+ 1,
        1
    )