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
moeconsult
Helper V
Helper V

Can't find Name

I want to create a calculated table to return earlier date if the logic is true but I am getting a error saying can't find of the columns.

 please see screenshort

Name.JPG

Table 2 = SUMMARIZE('Table',[Value],[Type],[State],[Date],IF(AND('Table'[Type]="cs-accelerator-CS Accelerator Assessment",'Table'[State]="Failed"),CONVERT(MIN([Date]),STRING),"No")
 
Thanks 
8 REPLIES 8
amitchandak
Super User
Super User

@moeconsult , Try like

 

Table 2 = SUMMARIZE('Table','Table'[Value],'Table'[Type],'Table'[State],'Table'[Date],"Measure",IF(AND('Table'[Type]="cs-accelerator-CS Accelerator Assessment",'Table'[State]="Failed"),CONVERT(MIN([Date]),STRING),"No"))

 

If your calculation (IF(...) ) work as measure it will work here.

If just want rename without any grouping then use selectcolumns

@amitchandak  thanks for your help, that works but returning everything from the main table I want to summarize , I would like to exclude Tpe, state as they are not making my output unique,

 

Please screenshot of what my output looks like, as you can see below, its returning two dates instead of returning just '14/01/2020' as the latest date:

Name.JPG

 

 

 

Please see below what I would like my output to look like :

 

ValueMeasure
ee6080d4-8942-ea11-a812-000d3a86d7a328/03/2020 
a392711c-151c-ea11-a811-000d3a86d7a3No
82ddcb3c-eb11-ea11-a811-000d3a86d545No
0dd45e8f-a507-ea11-a811-000d3a86d6ba26/02/2020
d476000d-d20a-ea11-a811-000d3a86d545no
5e91e211-cd0a-ea11-a811-000d3a86d54514/05/2020 

 

 

@moeconsult , try to have a measure like this

Measure =
VAR __id = MAX ( 'Table'[Value] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ), 'Table'[Value] = __id )
RETURN CALCULATE ( Max ( 'Table'[date] ), VALUES ( 'Table'[Value ), 'Table'[Value] = __id, 'Table'[date] = __date )

@amitchandak 

 

Name.JPG

 

I am not sure am doing wrong, Can I have it in a calculated column?

Hi,

 

Could you please share some sample data and the expected result as screenshots?

Expect your reply!

 

Best Regards,

Giotto

@moeconsult , Missed ]

Measure =
VAR __id = MAX ( 'Table'[Value] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ), 'Table'[Value] = __id )
RETURN CALCULATE ( Max ( 'Table'[date] ), VALUES ( 'Table'[Value] ), 'Table'[Value] = __id, 'Table'[date] = __date )

az38
Community Champion
Community Champion

Hi @moeconsult 

pay attention to arguments. before IF statement should be a new column name

https://docs.microsoft.com/en-Us/dax/summarize-function-dax


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Please can you help little bit further @az38 

 

Below is my formula but its not working , I dont want to incluce [type] and [State] in my summarized columns as it return duplicates in my table :

 

Table 2 = SUMMARIZE('Table',[Value],"Last",IF(AND('Table'[Type]="cs-accelerator-CS Accelerator Assessment",'Table'[State]="Failed"),CONVERT(MIN('Table'[Date]),STRING),"No"))
 
Name.JPG
 
What I want returned is two columns: Value and New column from the IF statement, any thought ?

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.