Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I hope somebody can help me with this setup I want to achieve in PowerBI.
I've 2 tables, a table tblServers & a table tblWaves. The tblServers lists out all servers & to which wave they belong. The tabel tblWaves gives for each wave some dates. Sometimes a wave only occures each quarter, some waves are more frequently (wave3 in this example). There's a one to many relationship between those 2 tables.
I want to show on my dashboard on what date the previous wave took place & when the next wave will take place, depending on todays date. So in this example (today is May 3rd) it would give this example.
Anybody an idea how to crack this one?
Solved! Go to Solution.
Hi @svendaems ,
Thanks for your concern about this case!
@svendaems Have you solved your problem? If not, please try this way:
Because today is not May 3rd, so I use May 3rd to test:
Previous Patching =
VAR PatchingName = tblPatching[Name]
VAR RelatedWave = RELATED(tblServers[Wave])
VAR TargetDate = CALCULATE(
MINX(
FILTER(
tblWaves,
tblWaves[Wave] = RelatedWave &&
tblWaves[Date] <= DATE(2024, 5, 3)
),
tblWaves[Date]
),
ALL(tblWaves)
)
RETURN TargetDate
Next Patching =
VAR PatchingName = tblPatching[Name]
VAR RelatedWave = RELATED(tblServers[Wave])
VAR TargetDate = CALCULATE(
MINX(
FILTER(
tblWaves,
tblWaves[Wave] = RelatedWave &&
tblWaves[Date] >= DATE(2024, 5, 3)
),
tblWaves[Date]
),
ALL(tblWaves)
)
RETURN TargetDate
Please change the
DATE(2024, 5, 3)
into
TODAY()
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @svendaems ,
Thanks for your concern about this case!
@svendaems Have you solved your problem? If not, please try this way:
Because today is not May 3rd, so I use May 3rd to test:
Previous Patching =
VAR PatchingName = tblPatching[Name]
VAR RelatedWave = RELATED(tblServers[Wave])
VAR TargetDate = CALCULATE(
MINX(
FILTER(
tblWaves,
tblWaves[Wave] = RelatedWave &&
tblWaves[Date] <= DATE(2024, 5, 3)
),
tblWaves[Date]
),
ALL(tblWaves)
)
RETURN TargetDate
Next Patching =
VAR PatchingName = tblPatching[Name]
VAR RelatedWave = RELATED(tblServers[Wave])
VAR TargetDate = CALCULATE(
MINX(
FILTER(
tblWaves,
tblWaves[Wave] = RelatedWave &&
tblWaves[Date] >= DATE(2024, 5, 3)
),
tblWaves[Date]
),
ALL(tblWaves)
)
RETURN TargetDate
Please change the
DATE(2024, 5, 3)
into
TODAY()
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@svendaems Try:
Previous Patching =
VAR __Today = TODAY()
VAR __Result = MAXX(FILTETER('tblWaves', [Date] <= __Today), [Date])
RETURN
__Result
Next Patching =
VAR __Today = TODAY()
VAR __Result = MINX(FILTETER('tblWaves', [Date] >= __Today), [Date])
RETURN
__Result
Thanks already for your reply. Unfortunlatly it's not working out as it should. It isn't looking at the related server, but giving for all servers the same date. It takes the first date that matches the formula (first previous date or first next date, related to today).
@svendaems Can you post your data as text? I also don't understand how you have a 1 to many relationship between those two tables. This *may* be it::
Previous Patching =
VAR __Today = TODAY()
VAR __Wave = MAX('tblServers'[Wave])
VAR __Result = MAXX(FILTETER('tblWaves', [Wave] = __Wave && [Date] <= __Today), [Date])
RETURN
__Result
Next Patching =
VAR __Today = TODAY()
VAR __Result = MINX(FILTETER('tblWaves', [Wave] = __Wave && [Date] >= __Today), [Date])
RETURN
__Result
You're right, I made a mistake in my question. It's a many to many relationship. I've added the data in a test pbx file, you can download it here.
I've tried also your second suggestion but that gives the same result.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |