Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a query I tried to resolve but I failed badly. I have a table below:
Date | LinkName |
15/08 | A |
16/08 | A |
17/08 | A |
18/08 | A |
19/08 | A |
20/08 | A |
21/08 | A |
15/08 | B |
16/08 | B |
17/08 | B |
18/08 | B |
19/08 | B |
20/08 | B |
21/08 | B |
22/08 | A |
23/08 | B |
24/08 | B |
25/08 | A |
26/08 | A |
27/08 | A |
and how i get a result like this
Date | find occurrence 7 times in 7 consecutive days |
15/08 | 0 |
16/08 | 0 |
17/08 | 0 |
18/08 | 0 |
19/08 | 0 |
20/08 | 0 |
21/08 | 2 |
22/08 | 0 |
23/08 | 0 |
24/08 | 0 |
25/08 | 0 |
26/08 | 0 |
27/08 | 0 |
I can get this problem in excel, but it is hard for me to do it in PowerBi. Iam trying my best to learn PowerBi. Hopefully anyone can help me solve this problem.
Solved! Go to Solution.
@Anonymous - Sorry, I made this more complicated than necessary. See Table (25) of attached PBIX below sig.
Column =
VAR __Table = FILTER('Table (25)',[LinkName]=EARLIER([LinkName]) && [Date]<=EARLIER([Date]) && [Date]>=EARLIER([Date])-7)
RETURN
IF(COUNTROWS(__Table)=7,1,0)
@Anonymous - Perhaps:
Column =
VAR __Table = SUMMARIZE(FILTER('Table',[LinkName]=EARLIER([LinkName]) && [Date]<=EARLIER([Date]) && [Date]>=EARLIER([Date])-7),[LinkName])
RETURN
IF(COUNTROWS(__Table)=7,1,0)
@Anonymous - Sorry, I made this more complicated than necessary. See Table (25) of attached PBIX below sig.
Column =
VAR __Table = FILTER('Table (25)',[LinkName]=EARLIER([LinkName]) && [Date]<=EARLIER([Date]) && [Date]>=EARLIER([Date])-7)
RETURN
IF(COUNTROWS(__Table)=7,1,0)
@Greg_Deckler yeah, U got it. I still have question, U used calculated collumn to solve this, but my table is very large ( ~ 500000 rows ). So Could we use Mesure instead of Calculated collumn.
@Anonymous - You could, that would look like this:
Column =
VAR __Link=MAX([LinkName])
VAR __Date=MAX([Date])
VAR __Table = FILTER('Table (25)',[LinkName]=__Link && [Date]<=__Date && [Date]>=__Date-7)
RETURN
IF(COUNTROWS(__Table)=7,1,0)
Would have to do some additional experimenting to get it to aggregate by Date (so you have just date in your visual and you want 2 to appear for A and B consequetive runs. This assumes you have LinkName in the visual. But, all that said, I *think* you will be happier with a column. This is because it runs a single time during data load. Otherwise, your measure will have to constantly recaculate while you are browing the report and that will be slooooooowwww!! 🙂
It returns 0 for all rows. Maybe sth wrong.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |