Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dom87326
Helper II
Helper II

Filter Value based on the MAX Date available and partially ignoring slicer

Hello, 

 

I have a dimUserAgg table that defines aggregated user activity data (number of logins and etc.) accumulated per month:

dimDate 1:* dimUserAgg (relationship not active)
dimDate 1:* Fact (active relationship)

 

I want to filter on the max Number of Logins available based on Date filters. Max Number of Logins is always the last month of the year. If date is set to 2020 March, I only want to return the Number of Logins for 2020 Dec, if it's 2021 May then 2021 Dec. 

 

Is there a way to write a DAX using the Calculate, MAX date function or any other syntax to arrive to this? 


Thanks in advance for any ideas.

 

dimDate

2021-11-13 16_31_33-PBI - Power BI Desktop.png

 

dimUserAgg

2021-11-13 16_27_25-New Microsoft Excel Worksheet - Excel.png

 

1 ACCEPTED SOLUTION

Hi @Dom87326 ,

 

Your Error means that you compare whole number type column by text type column. According to your statement, I think DimUserAgg[DateKey] is whole number type .

"VAR _LM =YEAR( _M ) & 12 & 01" in measure from VahidDM will return text. And you compare it with DimUserAgg[DateKey] in filter.

Measure =
VAR _M =
    MAX( DimDate[Date] )
VAR _LM =
    YEAR( _M ) & 12 & 01
RETURN
    CALCULATE(
        MAX( DimUserAgg[Number of Logins] ),
        FILTER( ALL( DimUserAgg ), DimUserAgg[DateKey] = _LM )
    )

Format Date column to yyyymmdd will return text type result as well. 

You can try this code.

Measure =
VAR _M =
    MAX( DimDate[Date] )
VAR _LM =
    YEAR( _M )*10000 + 1201
RETURN
    CALCULATE(
        SUM( DimUserAgg[Number of Logins] ),
        FILTER( ALL( DimUserAgg ), DimUserAgg[DateKey] = _LM )
    )

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Dom87326
Helper II
Helper II

Hello @VahidDM

 

I've added one more variable for Month. 

 

However with this syntax I get the following error: MdxScript(Model) (358, 57) Calculation error in measure : DAX comparison operations do not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

 

 

test_1 =
VAR _Y =
MAX( DimDate[Date] )
VAR _M =
MAX( DimDate[Date] )
VAR _LM =
YEAR( _Y ) & MONTH( _M ) & VALUE(01)
RETURN
CALCULATE(
MAX( DimUserAgg[Number of Logins] ),
FILTER( ALL( DimUserAgg ), DimUserAgg[%DATE_KEY] = _LM ))

 

 

I've created DimDate by using below code, while DimUserAgg[%DATE_KEY] is integer type from source. 

 

 

Date =
VAR MinYear = YEAR ( MIN ( 'Fact'[%DATE] ) )
VAR MaxYear = YEAR ( MAX ( 'Fact'[%DATE] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO ( ),
YEAR ( [Date] ) >= MinYear &&
YEAR ( [Date] ) <= MaxYear
)

 

 

Is it possible using FORMAT to change DimUserAgg[%DATE_KEY] type to date yyyymmdd?

Hi @Dom87326 ,

 

Your Error means that you compare whole number type column by text type column. According to your statement, I think DimUserAgg[DateKey] is whole number type .

"VAR _LM =YEAR( _M ) & 12 & 01" in measure from VahidDM will return text. And you compare it with DimUserAgg[DateKey] in filter.

Measure =
VAR _M =
    MAX( DimDate[Date] )
VAR _LM =
    YEAR( _M ) & 12 & 01
RETURN
    CALCULATE(
        MAX( DimUserAgg[Number of Logins] ),
        FILTER( ALL( DimUserAgg ), DimUserAgg[DateKey] = _LM )
    )

Format Date column to yyyymmdd will return text type result as well. 

You can try this code.

Measure =
VAR _M =
    MAX( DimDate[Date] )
VAR _LM =
    YEAR( _M )*10000 + 1201
RETURN
    CALCULATE(
        SUM( DimUserAgg[Number of Logins] ),
        FILTER( ALL( DimUserAgg ), DimUserAgg[DateKey] = _LM )
    )

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VahidDM
Super User
Super User

Hi @Dom87326 

 

Try this:

Measure =
VAR _M =
    MAX( DimDate[Date] )
VAR _LM =
    YEAR( _M ) & 12 & 01
RETURN
    CALCULATE(
        MAX( DimUserAgg[Number of Logins] ),
        FILTER( ALL( DimUserAgg ), DimUserAgg[DateKey] = _LM )
    )

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors