Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Historic Data by Week Number, Creating a Current Week X-Axis

I'm struggling to get data to show correctly in any chart visual yet the data shows fine in a table format. Most likely because they are 2 seperate tables. Below is the chart as it previously existed...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Yes, I can.

    Usually, only one date table is needed. Please refer to the snapshot below. There are two relationships while one is inactive.

    // We don't need to use userealtionship() for the active relationship.

_Incidents_Resolved = COUNTROWS(Incidents)
    _Incident_Arrivals =
CALCULATE (
    COUNTROWS ( Incidents ),
    USERELATIONSHIP ( Added_Date[DATEKEY], Incidents[Added_Date_Key] )
)

    Historic-Data-by-Week-Number-Creating-a-Current-Week-X-Axis

     

     

    Best Regards,

Anonymous's avatar
Anonymous
Not applicable
7 years ago

v-jiascu-msft Many Thanks for this. So here is my DAX

 

_Incident_Arrivals = CALCULATE(COUNTROWS(Incidents),USERELATIONSHIP(Added_Date[DATEKEY],Incidents[Added_Date_Key]))
 
and the same again for Incidents Resolved
 
_Incident_Resolved = CALCULATE(COUNTROWS(Incidents),USERELATIONSHIP(Closed_DateDATEKEY],Incidents[Closed_Date_Key]))
 
Sticking that in the Values and the WeekNum from my Calendar into the Axis gets me the chart below. Somthings not right as you can tell. Both Values are the same
 
Chart after UseRelationship DAX
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

Can you share your file? Please mask the sensitive parts first.

Why are there so many date tables like Added_Date, Colsed_Date? Only one date table is needed.

 

 

Best Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    v-jiascu-msft Again Thanks.  The 2 date tables are there as a colleague suggested as we can't use the one date table to relationshiop between both dates hence 2 tables. 

     

    I will now go away and prep a file i can share 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Can you access the file via this link 

     

    Power BI File

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Anonymous,

       

      Yes, I can.

      Usually, only one date table is needed. Please refer to the snapshot below. There are two relationships while one is inactive.

      // We don't need to use userealtionship() for the active relationship.

_Incidents_Resolved = COUNTROWS(Incidents)
      _Incident_Arrivals =
CALCULATE (
    COUNTROWS ( Incidents ),
    USERELATIONSHIP ( Added_Date[DATEKEY], Incidents[Added_Date_Key] )
)

      Historic-Data-by-Week-Number-Creating-a-Current-Week-X-Axis

       

       

      Best Regards,

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Anonymous ,

       

      Could you please mark the proper answers as solutions?

       

       

      Best Regards,

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        v-jiascu-msft Hello, I have just got around to testing this out. I will now mark the accepted solution 

         

         

        Thanks Again