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.
My problem is a little difficult to explain:
I use BI to show our fellows their corresponding dates, therefore I use the roles. One of the tables filtered for the role is a date table called "Schultage" (school days", so the fellows only see the their active working period. The DAX filter used in role definition is
This works great... until it comes to calculations. The full "Schultage"-Table starts at August, 1st, 2020 (01.08.2020 in german notation). And every calculation I use allways starts with this date, not the first day visible for the fellow. Which, in this example, is August, 4th, 2021 (04.08.2021).
Any idea how to solve this? Everything I tried had no effect ;-(
The numbers in red are what I want to get. It is the running total of "is schoolday", but only in the context of the visible part of "Schultage", not the whole table.
Solved! Go to Solution.
Hi @OliTFD ,
You can create a measure as below, please find the details in the attachment.
Schooldays Running Total =
VAR _mindate =
CALCULATE ( MIN ( 'Schultage'[Date] ), ALLSELECTED ( 'Schultage' ) )
VAR _schooldays =
CALCULATE (
COUNT ( 'Schultage'[Is schoolday] ),
FILTER (
ALLSELECTED ( 'Schultage' ),
'Schultage'[Is schoolday] = 1
&& 'Schultage'[Date] <= SELECTEDVALUE ( 'Schultage'[Date] )
&& 'Schultage'[Date] >= _mindate
)
)
RETURN
_schooldays
Best Regards
Dear Rean,
thanks a lot! I needed some additional measures to calculate the first working day from the Princiapaluser, and ad this measure to your idea. Now I get correct results.
First, I calculate the users mailadress:
Hi @OliTFD ,
You can create a measure as below, please find the details in the attachment.
Schooldays Running Total =
VAR _mindate =
CALCULATE ( MIN ( 'Schultage'[Date] ), ALLSELECTED ( 'Schultage' ) )
VAR _schooldays =
CALCULATE (
COUNT ( 'Schultage'[Is schoolday] ),
FILTER (
ALLSELECTED ( 'Schultage' ),
'Schultage'[Is schoolday] = 1
&& 'Schultage'[Date] <= SELECTEDVALUE ( 'Schultage'[Date] )
&& 'Schultage'[Date] >= _mindate
)
)
RETURN
_schooldays
Best Regards
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |