Can I use a DAX measure to filter a table visual instead of a Calculated Column?

[IM_TRYING_HERE]

So I know that typically this row level logic is best accomplished with a Calculated Column but I have restricted permissions and can only create Measures. I have been told when possible to use measures. Can I do this?

So I have a table visual that has three dates.
- move-out date
- end date
- As Of Date (which shows the month we are slicing for)

Is it possible to create a measure that will look at each row of that table visual and perform an if statement that says
If Move Out < End Date then Move Out Else End Date, then check that against the As Of Date and if it is less than or equal to the As Of date then 1 else 0
The hope is to be able to remove the rows that return a 0.

[v-pgoloju]

Hi @IM_TRYING_HERE,

Just following up to see if the responses provided by community member were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @IM_TRYING_HERE,

Just following up to see if the response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @IM_TRYING_HERE ,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Cookistador   for prompt and helpful response.

Just following up to see if the responses provided by community member were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Cookistador]

Hello

Yes it is, but you will have to use  a small workaround to achieve that, first you have to create your measure

Something like this should return what you need

Count_Rows_Meeting_Condition =
SUMX(
'Table', 
VAR MoveOut = 'Table'[move-out date]
VAR EndDate = 'Table'[end date]
VAR AsOfDate = MAX('Table'[As Of Date]) 
VAR MinDate = IF(MoveOut < EndDate, MoveOut, EndDate)
RETURN
IF(MinDate <= AsOfDate, 1, 0)
)

Then, in your table, you have to add your measure and in the filter of the visual, you hidde if your measure = 0

Know, if you do not want to see this column, you can hide it, your replace the name by a blank space and your use conditional formatting for the color

If you need more help, do not hesistate to ask 

[IM_TRYING_HERE]

Thanks for helping! I tried putting that measure in but it starting getting multiple rows like a relationship issue was occurring. Here is a picture of what I'm trying to do. I want to make it so the measure I create can be used to filter the table so that the only row remaining is the green one because it had not ended or moved out at the moment of the AsOf Date. I hope this helps

[Cookistador · Accepted solution]

Hi @IM_TRYING_HERE 

I did a test and it is working for me

What is the relationship between your calendar table and your Contract table ? 

Without this infomration, you can also create a disconnected date table, and use this measure:

Count_Rows_Meeting_Condition =

SUMX(

'Contract Table',

VAR MoveOut = 'Contract Table'[Move Out]

VAR EndDate = 'Contract Table'[end date]

VAR AsOfDate = SELECTEDVALUE('Date'[Date])

VAR MinDate = IF(MoveOut < EndDate, MoveOut, EndDate)

RETURN

IF(MinDate <= AsOfDate, 1, 0)

)

But, show us your data model if you prefer (or just the relationhip between the two tables)

[IM_TRYING_HERE]

This got me far enough to get a working measure. I had to adjust the VAR MoveOut because that date came from a different table so I had to use CALCULATE ( SELECTEDVALUE(OtherTable[MoveOut], USERELATIONSHIP ( ContractTable[IdKey],OtherTable[IdKey] ) ). Thanks for your help!