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

     

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

       

      • sabinocivita's avatar
        sabinocivita
        Frequent Visitor
        4 years ago

        Ok, very clear now, thanks a lot.