Find the number of regulatory ID occurrences in the new summary table

[Anonymous]

Hello Expert

I have a requlatory identification table with thousands of rows of data, I want to create a summary table and link the new summary table to the Regulatroy FACT problem via Reg ID. With the most recent date in the date column.

See image below

[v-shex-msft]

Hi @Anonymous,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[mh2587]

Number Consecutive Days =
VAR selecteddate =
SELECTEDVALUE ( 'Table'[Date] )
VAR lastnotworkeddate =
CALCULATE (
MAXX (
FILTER ( Date, ISBLANK ( CALCULATE ( COUNTROWS ( 'Regulatory Issue ID' ) ) ) ),
Table[Date]
),
Table[Date] < selecteddate
)
VAR diff =
DATEDIFF ( lastnotworkeddate, selecteddate, DAY )
RETURN
IF ( COUNTROWS ( 'Regulatory Issue ID' ) > 0, IF ( diff > 1, diff ) )

Try this one hope it will solve the issue 

[YukiK]

Are you looking to keep the last row of each regulatory issue ID? I'd do this in Power Query than in DAX...

You can select a column and use "keep rows" to keep only the last row:

If this helps, please give it a thums up and select as a solution 🙂

[Anonymous]

Hi I am looking to keep only the last date but - how do you count the number of time/occurance that same regulatory id has re-occurred.

[YukiK · Accepted solution]

Gotcha. This article should answer what you're asking to do!

https://radacad.com/grouping-in-power-query-getting-the-last-item-in-each-group

Lete me know if this helps!