Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Graph axis = two columns filtered

Hello,   I have a database with three columns: id, date created and end date. I need to make a graph by month/year and the value is the count of ids that its date created is before or equal to that...
Need Help
Tutorial Requests
  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    Hope you have a date dimension.

     

    The method i know is to create a new column in Date dimension as below.

     

    Column =
CALCULATE (
    COUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[date created] <= 'Table 2'[Date]
            && 'Table'[end date] >= 'Table 2'[Date]
    )
)

     

     

    Now use the month column in axis and (Max of New_column) as value in the graph.

    If this helps, mark it as a solution

    Kudos are nice too.

VasTg's avatar
VasTg
Memorable Member
6 years ago

Anonymous 

 

Hope you have a date dimension.

 

The method i know is to create a new column in Date dimension as below.

 

Column =
CALCULATE (
    COUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[date created] <= 'Table 2'[Date]
            && 'Table'[end date] >= 'Table 2'[Date]
    )
)

 

 

Now use the month column in axis and (Max of New_column) as value in the graph.

If this helps, mark it as a solution

Kudos are nice too.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

That solved it, yes, thank you.

 

But if I want to have the ids that were filtered, how would I do it? I tried adding a new column but I can't get around the "the expression refers to multiple columns" error.

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

      

    Add it to the filter condtition.

     

     

    CALCULATE(COUNT('Table'[id]),FILTER('Table','Table'[date created]<='Table 2'[Date] && 'Table'[end date]>='Table 2'[Date] && 'Table'[id] = 123))

     

     

    If it helps, mark it as a solution

    Kudos are nice too

     

     

    Anonymous

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Oh no that's not what I meant

       

      I want to print all the ids that were successfully filtered in that other column

       

      Ex: if the count is 4, I wanna print 123;456;789;147

       

      Doesn't really matter the way it's printed, if it's all in the same column as in "123;456" or one id in every column, but I need the values