Forum Discussion

sabinocivita's avatar
sabinocivita
Frequent Visitor
4 years ago
Solved

Complex query

Hi all,

I have a table with 3 columns: numjob, openedjobdate, closedjobdate. I want to have a visual matrix that has months as rows and as values distinct count of job opened and distinct count of job closed for each month.

How to do it?

 

Thanks in advance 

Need Help
  • PC2790's avatar
    PC2790
    4 years ago

    It can be like:

    Count(Opendate) = Count(Table[numjob])
    Count(Closedate) =  CALCULATE(count(Table[numjob]),USERELATIONSHIP(Table[closedjobdate],CalendarTable[Date]))

     

4 Replies

  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    First of all you need to have a Calendar table with which your this table will have with.

    Refer - Power BI Date or Calendar Table Best Method: DAX or Power Query? - RADACAD

    Then have two relationship, one between Date from Calendar and openedjobdate(active)

    The other between Date from Calendar and closedjobdate(inactive)

    Then create two measures to count the number of jobs.

    Please note for closeddate you will have to use USERELATIONSHIP dax

     

    And at last in matrix, take month from Calendar table and the measures like this:

     

    • sabinocivita's avatar
      sabinocivita
      Frequent Visitor
      4 years ago

      Hi PC2790,

      thanks for answer.

      When you say "Then create two measures to count the number of jobs." on which columns are they based on? Cause I have only one column for numjob

      • PC2790's avatar
        PC2790
        Community Champion
        4 years ago

        It can be like:

        Count(Opendate) = Count(Table[numjob])
        Count(Closedate) =  CALCULATE(count(Table[numjob]),USERELATIONSHIP(Table[closedjobdate],CalendarTable[Date]))