Forum Discussion
Clustered column chart
Hi pauldago,
You count them by different columns and different date. So a date table is essential. First we create a new date table which will be our x-axis. Then create two measures.
1. Create a date table (image 1).
Date =
CALENDAR ( "2001-01-01", "2017-12-31" )2. Create two measures
Deleted =
CALCULATE (
COUNTROWS (
FILTER ( 'Table', 'Table'[STATUS DATE].[Year] = MIN ( 'Date'[Date].[Year] ) )
),
'Table'[STATUS] = "deleted"
)Created =
COUNTROWS (
FILTER ( 'Table', 'Table'[CREATION DATE].[Year] = MIN ( 'Date'[Date].[Year] ) )
)
Hi pauldago,
You count them by different columns and different date. So a date table is essential. First we create a new date table which will be our x-axis. Then create two measures.
1. Create a date table (image 1).
Date =
CALENDAR ( "2001-01-01", "2017-12-31" )
2. Create two measures
Deleted =
CALCULATE (
COUNTROWS (
FILTER ( 'Table', 'Table'[STATUS DATE].[Year] = MIN ( 'Date'[Date].[Year] ) )
),
'Table'[STATUS] = "deleted"
)
Created =
COUNTROWS (
FILTER ( 'Table', 'Table'[CREATION DATE].[Year] = MIN ( 'Date'[Date].[Year] ) )
)
Hi v-jiascu-msft,
thanks a lot for your solution, it works well!! Attached you can see the report. Even if I don't understand why we have to use the MIN function for the Date... Can you give me an explanation?
Furthermore I'm tryng to Filter the list of the ITEMS based on the year that I select in the Clustered column chart, but it doesn't work... You can see in the attached image that the filter is active!!!
Thanks
Hi pauldago,
You are welcome. I am so glad it helps.
To the first question, we don't have to use the MIN function. When the DAX (a funcional language by which the measure is created) is running, there is a context. For example here, when the measure "Created" run, the context is YEAR. For each value in YEAR, the measure runs once. Everytime the measure runs, there is only one YEAR. So we can use the MAX funciton too. You can try to remove the MIN function in the measure. You will understand this when you read the error message. This may be not professional, but it works like this.
To the second question, the new table DATE doesn't have relationship with the other table. So filter doesn't work. Due to there are two date column here, any relationship isn't proper. Please have a look at the image part 1, if you choose year 2006, you will miss data of "deleted 2006". There is another way to try. If you can format your data like the one in the image part 2 (cut and paste...), you can filter them. You don't even need a measure. (filter the PRODUCTION).