Clustered column chart

[pauldago]

Hi guys, I need to have your help to solve my issue with Clustered column chart in Power BI. I have an ITEMS list in Excel with the CREATION date and the STATUS date. I'd like to create a Clustered column chart in Power BI to compare how many ITEMS have been created and deleted during the years. Attacched you can see an example (with a few items) I realized in excel, using a table manually feeded.

Thanks a lot for your help.

[v-jiascu-msft · Accepted solution]

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] ) )
)

[pauldago]

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

[v-jiascu-msft]

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).

[Greg_Deckler]

You should just be able to create two measures like:

CREATED = CALCULATE(COUNTROWS(Table),[STATUS]="PRODUCTION")


DELETED = CALCULATE(COUNTROWS(Table),[STATUS]="DELETED")

[pauldago]

Hi smoupre, thanks a lot for your help. I can use your suggestion for the DELETED Items as I have it as a Status, but I don't have the information CREATED as a Status. I have only the Creation Date column. 

Thank you

[EminUZUN]

Do you have status column for all but empty?
If so, you may use <> (does not equal to)

CREATED= CALCULATE(COUNTROWS(Table),[STATUS]<>"DELETED")