Forum Discussion
SQL Statement to Dynamically Use Max Value in a Column
I have a date column in my data, but the format and data type is not a date. The format is 01220102 which is January 02, 2022. I want to right a SQL statement in the adavanced options that will pull records that are the maximum value - X. For example, only return records ColumnX >= (Max(ColumnX)-200).
Any help is appreciated!
Hi jackofalltrades ,
Are you connecting to the sql data source on the desktop? If so, you can try to use dax.
Create a measure.
Measure=VAR _MAXVALUE=MAXX(ALL(TABLE),ColumnX) VAR _MAX200=_MAXVALUE-200 return IF(MAX(ColumnX)>=_MAX200&&MAX(ColumnX)<=_MAXVALUE,1,BLANK())Or a column.
Column=VAR _MAXVALUE=MAXX(TABLE,ColumnX) VAR _MAX200=_MAXVALUE-200 return IF(ColumnX>=_MAX200&&ColumnX<=_MAXVALUE,1,BLANK())If you want to use sql formula in SQL, then you need to go to the SQL forum.
If I have misunderstood your meaning, please provide some simple sample data without privacy information and your desired output.
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi jackofalltrades ,
Are you connecting to the sql data source on the desktop? If so, you can try to use dax.
Create a measure.
Measure=VAR _MAXVALUE=MAXX(ALL(TABLE),ColumnX) VAR _MAX200=_MAXVALUE-200 return IF(MAX(ColumnX)>=_MAX200&&MAX(ColumnX)<=_MAXVALUE,1,BLANK())Or a column.
Column=VAR _MAXVALUE=MAXX(TABLE,ColumnX) VAR _MAX200=_MAXVALUE-200 return IF(ColumnX>=_MAX200&&ColumnX<=_MAXVALUE,1,BLANK())If you want to use sql formula in SQL, then you need to go to the SQL forum.
If I have misunderstood your meaning, please provide some simple sample data without privacy information and your desired output.
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.I will move to the SQL forum. My apologies for posting in the wrong area. Thanks!
