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
Icon for Community Champion rankCommunity 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