Forum Discussion

OliTFD's avatar
OliTFD
Regular Visitor
4 years ago
Solved

How to work with tables using security roles: Calculations only for fitting dates

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.



Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • OliTFD's avatar
    OliTFD
    Regular Visitor
    4 years ago

    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:

    Aktiver Fellow = Calculate(MIn(Fellows[Fellow-Mailadresse]))
    Then, I get his first working day:
    Aktiver Fellow erster Arbeitstag = LOOKUPVALUE(Fellows[Erster Arbeitstag],Fellows[Fellow-Mailadresse],'AA Measures'[Aktiver Fellow])
     
    This could be done in one calculation, sure 😉

    Then I add this value to your calculation as minimum date:
    Schooldays Running Total =
    VAR _mindate =
    CALCULATE ( 'AA Measures'[Aktiver Fellow erster Arbeitstag], 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
    If( _schooldays-40 < 0,0,_schooldays-40)
    In the last line, there is some minor addition: I only count after working day 40.
    Thanks a lot for your help!