Need help with some measure/columns

[FrancescoSCP]

Hi all,

i have a simple model with one fact table and two dimensions, calendar and projects.

I have to calculate some columns/measure:

1. A column or measure that calculate if a project (from projects table) is closed or not based on the year selected. For that i have to check the MIN(date) of calendar table and compare with the end_date of my projects table: if the end_date is prior to the MIN(date) than it's closed, else it's running. I've done it with a measure, beacuse with a column i can't use the ISFILTERED function, and it seems working.
2. A column that calculate a state for every project table: so if a project it's running (point n.1) and category is 1 than "running projects", if a project is running and category is 2 then "custom running projects" and so on. I've tested it with a calculated column but it doesent work when i compare values with measure created at point 1.

Any idea?

I can do a simple .pbix file it it's needed.

Regards, Francesco

[v-angzheng-msft · Accepted solution]

Hi, @FrancescoSCP 

Yes, you cannot create a dynamic calculated column based on a measure.

The project_state column in your file, if you want it to change dynamically, then you need to use the measure instead of the calculated column.
And you really can't put the measure in the column of the matrix, it is recommended that you consider using the table visual, you may need to change the data structure if necessary to get the expected result you want.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FrancescoSCP]

Yes, it seems that i can't achive my goal only with measures/columns so i've changed the data model to do this. Thanks anyway for your support.

Regards.

Francesco

[FrancescoSCP]

No one?

[PaulDBrown]

Can you post a depiction of the expected result?

[FrancescoSCP]

Any suggestion?

Regards.

Francesco

[FrancescoSCP]

The column project_state in the .pbix file that i've uploaded has to work. It seems that i can't create a dynamic calculated column based on a measure

[FrancescoSCP]

Here's a simple .pbix file with an example https://we.tl/t-fHs3xZ1pxl 

As you can see the Running_Closed measeure works good: if no year is selected than it's "*" else it shows the correct value.

The Project_State column isn't working. I must use a column because i have to use it in a Matrix on columns to split the value.

Any ideas?