Solved: Use variable as a filter in CALCULATE - error

Anonymous · ‎04-11-2022

I need to calculate the amount of active students going back in time.

I have one date column, with an event type for each student.

In order to see if they are active, it requires a series of datediff calculations. But first I had to calculate the dates I needed out from the single date column. I tried this using variables:

B TEST 0704 =
var first_exam = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]),
'Demand'[Event Type] = 4 || 'Demand'[Event Type] = 5 || 'Demand'[Event Type] = 9 || 'Demand'[Event Type] = 12 || 'Demand'[Event Type] = 21 || 'Demand'[Event Type] = 22 || 'Demand'[Event Type] = 23 || 'Demand'[Event Type] = 24 || 'Demand'[Event Type] = 25 || 'Demand'[Event Type] = 26 || 'Demand'[Event Type] = 27 || 'Demand'[Event Type] = 28)

var latest_exam = CALCULATE(MAX('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]),
'Demand'[Event Type] = 4 || 'Demand'[Event Type] = 5 || 'Demand'[Event Type] = 9 || 'Demand'[Event Type] = 12 || 'Demand'[Event Type] = 21 || 'Demand'[Event Type] = 22 || 'Demand'[Event Type] = 23 || 'Demand'[Event Type] = 24 || 'Demand'[Event Type] = 25 || 'Demand'[Event Type] = 26 || 'Demand'[Event Type] = 27 || 'Demand'[Event Type] = 28)

var Accepted_date = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]), 'Demand'[Event Type] = 1)

var Doctor_date = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]), 'Demand'[Event Type] = 16)

var current_date = max('Date'[Date])

var exam_passed = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]),'Demand'[Event Type] = 10)

var theory_passed = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]),'Demand'[Event Type] = 4)

return

calculate(
distinctcount( 'Demand'[citizenid] ),
Kategori[Kategori] = "B",
'Demand'[Application_paid?] = TRUE(),
ISBLANK(exam_passed),
not(isblank(Doctor_date)),
not(ISBLANK(Accepted_date)),
datediff(first_exam, latest_exam,DAY) > 0 && datediff(first_exam, latest_exam,DAY) < 92 || datediff(Accepted_date, current_date,DAY) < 183,
datediff(Accepted_date, first_exam, DAY) > 0 && datediff(Accepted_date, first_exam, DAY) < 183 || datediff(Accepted_date, current_date,DAY) < 183,
datediff(Doctor_date, current_date, DAY) < 365,
datediff(latest_exam, current_date,DAY) > 0 && datediff(latest_exam, current_date,DAY) < 92 || datediff(Accepted_date, current_date,DAY) < 183

However I received an error message along the lines of: "True/False expression does not give one specific column. Every True/False expression, that is used as a table-filter expression must refer to exactly one column".

Is there any way to fix this measure or get around the error?

Whitewater100 · ‎04-11-2022

Hello:

Thisisn't directly answering yourquestion, but hopefully a much easier way to solve for any analysis you might want, with minimal DAX. Designing the data model wit Star Schema. I'll paste the file link here and hopefully this approach could work for you. I put in just a few basic measures for examples.

https://drive.google.com/file/d/17hxnOKiykG21T-NvbT0BbVbTRFY2t2cM/view?usp=sharing

View solution in original post

Whitewater100 · ‎04-11-2022

Hello:

Thisisn't directly answering yourquestion, but hopefully a much easier way to solve for any analysis you might want, with minimal DAX. Designing the data model wit Star Schema. I'll paste the file link here and hopefully this approach could work for you. I put in just a few basic measures for examples.

https://drive.google.com/file/d/17hxnOKiykG21T-NvbT0BbVbTRFY2t2cM/view?usp=sharing

Anonymous · ‎04-12-2022

Hi there,

This is useful, thank you for that. This is definitely something I should implement.

Samarth_18 · ‎04-11-2022

HI @Anonymous ,

Its bit difficult to answer without sample data with expected output but you can try this:-

B TEST 0704 =
VAR first_exam =
    CALCULATE (
        MIN ( 'Demand'[CreatedDate] ),
        ALL ( 'Demand' ),
        'Demand'[CitizenId] = MAX ( 'Demand'[CitizenId] ),
        'Demand'[Event Type] = 4
            || 'Demand'[Event Type] = 5
            || 'Demand'[Event Type] = 9
            || 'Demand'[Event Type] = 12
            || 'Demand'[Event Type] = 21
            || 'Demand'[Event Type] = 22
            || 'Demand'[Event Type] = 23
            || 'Demand'[Event Type] = 24
            || 'Demand'[Event Type] = 25
            || 'Demand'[Event Type] = 26
            || 'Demand'[Event Type] = 27
            || 'Demand'[Event Type] = 28
    )
VAR latest_exam =
    CALCULATE (
        MAX ( 'Demand'[CreatedDate] ),
        ALL ( 'Demand' ),
        'Demand'[CitizenId] = MAX ( 'Demand'[CitizenId] ),
        'Demand'[Event Type] = 4
            || 'Demand'[Event Type] = 5
            || 'Demand'[Event Type] = 9
            || 'Demand'[Event Type] = 12
            || 'Demand'[Event Type] = 21
            || 'Demand'[Event Type] = 22
            || 'Demand'[Event Type] = 23
            || 'Demand'[Event Type] = 24
            || 'Demand'[Event Type] = 25
            || 'Demand'[Event Type] = 26
            || 'Demand'[Event Type] = 27
            || 'Demand'[Event Type] = 28
    )
VAR Accepted_date =
    CALCULATE (
        MIN ( 'Demand'[CreatedDate] ),
        ALL ( 'Demand' ),
        'Demand'[CitizenId] = MAX ( 'Demand'[CitizenId] ),
        'Demand'[Event Type] = 1
    )
VAR Doctor_date =
    CALCULATE (
        MIN ( 'Demand'[CreatedDate] ),
        ALL ( 'Demand' ),
        'Demand'[CitizenId] = MAX ( 'Demand'[CitizenId] ),
        'Demand'[Event Type] = 16
    )
VAR current_date =
    MAX ( 'Date'[Date] )
VAR exam_passed =
    CALCULATE (
        MIN ( 'Demand'[CreatedDate] ),
        ALL ( 'Demand' ),
        'Demand'[CitizenId] = MAX ( 'Demand'[CitizenId] ),
        'Demand'[Event Type] = 10
    )
VAR theory_passed =
    CALCULATE (
        MIN ( 'Demand'[CreatedDate] ),
        ALL ( 'Demand' ),
        'Demand'[CitizenId] = MAX ( 'Demand'[CitizenId] ),
        'Demand'[Event Type] = 4
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Demand'[citizenid] ),
        Kategori[Kategori] = "B",
        FILTER (
            'Demand',
            'Demand'[Application_paid?] = TRUE ()
                && ISBLANK ( exam_passed )
                && NOT ( ISBLANK ( Doctor_date ) )
                    && NOT ( ISBLANK ( Accepted_date ) )
                        && (
                            DATEDIFF ( first_exam, latest_exam, DAY ) > 0
                                && DATEDIFF ( first_exam, latest_exam, DAY ) < 92
                        )
                        || DATEDIFF ( Accepted_date, current_date, DAY ) < 183
                            && (
                                DATEDIFF ( Accepted_date, first_exam, DAY ) > 0
                                    && DATEDIFF ( Accepted_date, first_exam, DAY )
                            ) < 183
                        || DATEDIFF ( Accepted_date, current_date, DAY ) < 183
                            && DATEDIFF ( Doctor_date, current_date, DAY ) < 365
                            && (
                                DATEDIFF ( latest_exam, current_date, DAY ) > 0
                                    && DATEDIFF ( latest_exam, current_date, DAY ) < 92
                            )
                        || DATEDIFF ( Accepted_date, current_date, DAY ) < 183
        )
    )

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Anonymous · ‎04-11-2022

@Samarth_18 Thanks for your response

This gets around the error message, but the filters themseleves are not actually working.

I unfortunately cant share the data, but I can give an example of what it looks like:

ID	Name	Event Type	Event Type no.	Event Date
1000001	John Smith	Doctor Certification	16	01-12-2021
1000001	John Smith	Exam Passed	10	01-03-2022
1000001	John Smith	Exam Failed	7	31-01-2022
1000001	John Smith	Did not show up to exam	6	10-01-2022
1000001	John Smith	Application Accepted	1	30-11-2021
1000005	Sarah Adams	Exam Failed	7	18-01-2022
1000005	Sarah Adams	Did not show up to exam	6	30-01-2022
1000005	Sarah Adams	Application Accepted	1	01-12-2021
1000005	Sarah Adams	Doctor Certification	16	01-12-2021
1000005	Sarah Adams	Exam Failed	7	20-02-2022
1000010	David Jones	Application Accepted	1	01-06-2021
1000010	David Jones	Doctor Certification	16	01-06-2021
1000020	Kirsty Roberts	Application Accepted	1	01-03-2022
1000020	Kirsty Roberts	Doctor Certification	16	01-03-2022
1000022	James Bond	Application Accepted	1	01-02-2022
1000022	James Bond	Doctor Certification	16	02-02-2022
1000022	James Bond	Exam Failed	7	15-03-2022

In this scenario David Jones and John Smith should not be considered as active, John Smith has passed the exam, and David Jones has not done anything in the last 183 days. So there should be 3 active students.

Please let me know if there is anything else you may need