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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mblas
New Member

Measure within a measure ist not working

Hi,

 

I have a datatable that looks like this

NAME | Date

A         | 1.1.2021

B         | 1.2.2021

B         | 1.3.2021

B         | 1.2.2021

B         | 1.3.2021

B         | 1.1.2021

 

I want to show all names with the second highest date (later I have to do it for the third highest date and so on).

 

1) I have created a measure that gives me the second highest date:

SecondLatest = DATEVALUE(MINX(FILTER(VALUES('myDB'[Date]), RANKX(VALUES('myDB'[Date]), CALCULATE(MAX('myDB'[Date])),,DESC)=2), CALCULATE(MAX('myDB'[Date]))))

 

This is working fine and I get "1.2.2021" as a result!

 

2) I want to get all data with this date therefore I create another measure

data = CALCULATE(COUNT('myDB'[Name]),FILTER('myDB','myDB'[Date] = "1.2.2021"))

-> this is also working fine

 

BUT here is my problem:

data = CALCULATE(COUNT('myDB'[Name]),FILTER('myDB','myDB'[Date] = [SecondLatest]))

-> ist not working and I don't know why

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Try:

Names on max date -1 =
VAR MxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
VAR MxDPrev =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < MxDate )
    )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Name] ),
        FILTER ( 'Table', 'Table'[Date] = MxDPrev )
    )

 

and you get:

maxdate.PNG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
mblas
New Member

that is working fine! 🙂

Thank you very much!!!

PaulDBrown
Community Champion
Community Champion

Try:

Names on max date -1 =
VAR MxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
VAR MxDPrev =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < MxDate )
    )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Name] ),
        FILTER ( 'Table', 'Table'[Date] = MxDPrev )
    )

 

and you get:

maxdate.PNG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors