Forum Discussion
Query IDs from previous quarter
Anonymous
Your Effective Date field must not filter the table from which you bring data into the visual for this to work. It's obvious why this should be so. If your selection filters data in the table, then... your visual will only show the data visible in the current context or a subset of it, not a superset. There are different ways to make it work. I can't see the model, so I'll tell you how I'd most likely do it. I would create a separate table with Effective Dates only that would be either disconnected from the fact table or had an inactive relationship with the fact table. Then I would create a slicer out of Effective Dates and the measure. Let's assume that the table is disconnected. The measure would be:
// Best Practice: All columns in a fact table // MUST be hidden and slicing is performed only // through dimensions. Deviate from this and... // you'll be in for nasty suprises sooner or // later. [Show Row] = // Efective Dates must be a proper date table // marked as a date table in the model. If // you want to show only certain dates to the // user in a slicer you can have a field in // the table that will enable you to filter // for the dates in the slicer visual. // I assume here that you are placing the Date // field in the slicer and this in fact is an // effective date. Any other setup and you must // adjust the code accordingly. var vIsOneEffectiveDateSelected = HASONEVALUE( 'Effective Dates'[Date] ) // Assuming that ID's are in the fact table, FT. var vOneIdIsInScope = ISINSCOPE( FT[ID] ) var vResult = if( vOneIdIsInScope && vIsOneEffectiveDateSelected, var vCurrentEffectiveDate = SELECTEDVALUE( FT[EffectiveDate] ) var vPrevQtrStart = MIN( PREVIOUSQUARTER( 'Effective Dates'[Date] ) ) var vPrevQtrEnd = MAX( PREVIOUSQUARTER( 'Effective Dates'[Date] ) ) var vIDQualifies = vPrevQtrStart <= vCurrentEffectiveDate && vCurrentEffectiveDate <= vPrevQtrEnd RETURN vIDQualifies ) return 1 * vResult
Thanks for replying amitchandak , I've already tried Time intelligence function as you suggested. But, it won't solve the problem. Time intelligence and Date table will only provide the logic for filtering but I'm trying to retrieve rows from previous quarter and I want to know what's the right DAX function which can do that.
Anonymous
What you're trying to do requires one thing only: a logical measure that returns True (1) when a row from the underlying table qualifies and False (0) otherwise. Once you've got this measure, you can use the Filter Pane to set a condition for row visibility based on the measure (which, of course, will be reacting to your silcer selections). Note that real logical functions (i.e., such that really return the bool values) will not work in the Filter Pane. You have to return an integer 0/1 from them.
Anonymous Thanks for replying. Based on your guidance, I created the following DAX measure:
row select = VAR _d1 = SELECTEDVALUE(Sheet1[EffectiveDate]) RETURN IF(MAX(Sheet1[EffectiveDate])< _d1,1,0)If the EffectiveDate for current row is less than the selected EffectiveDate, then the returned value is 1 or else 0. But the MAX doesn't seem to work on table level instead of row level. Can you please suggest the appropriate modification (or correct function to use) so the flags are calculated on row level.
Anonymous
Your Effective Date field must not filter the table from which you bring data into the visual for this to work. It's obvious why this should be so. If your selection filters data in the table, then... your visual will only show the data visible in the current context or a subset of it, not a superset. There are different ways to make it work. I can't see the model, so I'll tell you how I'd most likely do it. I would create a separate table with Effective Dates only that would be either disconnected from the fact table or had an inactive relationship with the fact table. Then I would create a slicer out of Effective Dates and the measure. Let's assume that the table is disconnected. The measure would be:
// Best Practice: All columns in a fact table // MUST be hidden and slicing is performed only // through dimensions. Deviate from this and... // you'll be in for nasty suprises sooner or // later. [Show Row] = // Efective Dates must be a proper date table // marked as a date table in the model. If // you want to show only certain dates to the // user in a slicer you can have a field in // the table that will enable you to filter // for the dates in the slicer visual. // I assume here that you are placing the Date // field in the slicer and this in fact is an // effective date. Any other setup and you must // adjust the code accordingly. var vIsOneEffectiveDateSelected = HASONEVALUE( 'Effective Dates'[Date] ) // Assuming that ID's are in the fact table, FT. var vOneIdIsInScope = ISINSCOPE( FT[ID] ) var vResult = if( vOneIdIsInScope && vIsOneEffectiveDateSelected, var vCurrentEffectiveDate = SELECTEDVALUE( FT[EffectiveDate] ) var vPrevQtrStart = MIN( PREVIOUSQUARTER( 'Effective Dates'[Date] ) ) var vPrevQtrEnd = MAX( PREVIOUSQUARTER( 'Effective Dates'[Date] ) ) var vIDQualifies = vPrevQtrStart <= vCurrentEffectiveDate && vCurrentEffectiveDate <= vPrevQtrEnd RETURN vIDQualifies ) return 1 * vResultIt's easy to fix. Instead of the above, you can use
MAXX( PREVIOUSQUARTER( 'Effective Dates'[Date] ), 'Effective Dates'[Date] )Same with MIN: just replace MAXX with MINX in the above line.