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 am currently working at updating Tabular model hosted on-prem on a SASS server version 13.0. The tabular model is version 1200. We have complex logic created in our measures depending of counting rules from parameters selected in disconnected tables.
The model was created using snowflake ‘like’ schema: It can be simplified as below:
We have several DAX expressions based on the below code:
Admission[Number of Clients] :=
VAR MinDate = MIN ( 'Date'[Full Date] )
VAR MaxDate = MAX ( 'Date'[Full Date] )
VAR AdmissionDate =
FILTER (
Admission,
SWITCH (
TRUE (),
VALUES ( 'Counting Rules'[Counting Rule] ) = "Starts",
Admission[Start Date] >= MinDate && Admission[Start Date] <= MaxDate,
VALUES ( 'Counting Rules'[Counting Rule] ) = "Ends",
Admission[End Date] >= MinDate && Admission[End Date] <= MaxDate,
VALUES ( 'Counting Rules'[Counting Rule] ) = "Active",
Admission[Start Date] <= MaxDate && Admission[End Date] >= MinDate,
0
)
)
RETURN
IF (
COUNTROWS ( VALUES ( 'Counting Rules'[Counting Rule] ) ) = 1,
CALCULATE (
DISTINCTCOUNT ( 'Admission'[ClientKey] ),
FILTER (
AdmissionDate,
IF ( RELATED ( 'Clients'[Date Of Birth] ) <= MAX ( 'Date'[Full Date] )
&& ( ISFILTERED ( 'Client Age'[Age Band] ) || ISFILTERED ( 'Client Age'[Age] ) ),
IF ( COUNTROWS ( VALUES ( 'Age Counting Rule'[Age Counting Rule] ) ) = 1,
INTERSECT (
VALUES ( 'Clients Age'[Age] ),
SELECTCOLUMNS (
ADDCOLUMNS (
VALUES ( Admission[ClientKey] ),
"@Age",
ROUNDDOWN (
DATEDIFF (
CALCULATE ( MAX ( 'Clients'[Date Of Birth] ) ),
VAR AdmissionDateFiltered =
IF (
VALUES ( 'Age Counting Rule'[Age Counting Rule] ) = "Age Min",
CALCULATE ( MIN ( Admission[Start Date] ), AdmissionDate, EARLIER ( Admission[ClientKey] ) = Admission[ClientKey] ),
CALCULATE ( MAX ( Admission[End Date] ), AdmissionDate, EARLIER ( Admission[ClientKey] ) = Admission[ClientKey] )
)
RETURN
IF ( VALUES ( 'Age Counting Rule'[Age Counting Rule] ) = "Age Min",
IF ( AdmissionDateFiltered < MIN ( 'Date'[Full Date] ) && NOT ISBLANK ( AdmissionDateFiltered ),
MIN ( 'Date'[Full Date] ),
AdmissionDateFiltered
),
IF ( AdmissionDateFiltered > MAX ( 'Date'[Full Date] ) && NOT ISBLANK ( AdmissionDateFiltered ),
MAX ( 'Date'[Full Date] ),
AdmissionDateFiltered
)
),
DAY
) / 365.25,
0
)
),
"Age", [@Age]
)
),
BLANK ()
),
TRUE ()
) //GOM Status
&& IF (
ISFILTERED ( 'Guardianship Status'[Guardianship Type] ) || ISFILTERED ( 'Guardianship Status'[Guardianship Type Description] ) || ISFILTERED ( 'Guardianship Status'[Under Guardianship] ),
CONTAINS (
VALUES ( 'Guardianship Status'[Guardianship Type] ),
'Guardianship Status'[Guardianship Type],
[Child Protection Order Active]
),
TRUE ()
)
)
),
BLANK ()
)
The age calculation portion is giving us the ability calculate the minimum or maximum age within a reporting period where a client was admitted so we can report on age client age profils.
When using Tabular editor, we get an error message "Function 'INTERSECT' does not support joining a column of type Integer with a column of type ."
My understanding: It is due to the function Intersect between the disconnected table 'Client Age'[Age] and the calculated column [@Age]. [@Age] doesn't have a type and therefore the error is created.
Has anybody encountered this error, or has an idea on how to resolve the problem?
Solved! Go to Solution.
I have resolved my issue by upgrading the model to 1500 version - PowerBI Service.
I have resolved my issue by upgrading the model to 1500 version - PowerBI Service.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |