Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pedrohenriquewe
Regular Visitor

DAX to create a Graphic (DAILY WIP) with Jira Software Database (help please)

Hello everyone,

Btw, I do apreciate your help for trying to solve my problem. 

All dates are in format: DD/MM/YYYY
The columns in the same colour are the relashionship between tables.

My Data (3 tables):

Table: History (fact)
keyStatusStatus Date
X-1Backlog01/01/2023
X-1In Progress02/01/2023
X-1Ready to Release05/01/2023
X-1Done06/01/2023
X-2Backlog03/01/2023
X-2In Progress04/01/2023
X-2Ready to Release10/01/2023
X-2Done11/01/2023
X-3Backlog05/01/2023
X-3In Progress07/01/2023

 

Table: Issues (dimension)
keyCurrent StatusResolved Date
X-1Done06/01/2023
X-2Done11/01/2023
X-3In Progress 

* Colunm key is the primary key

Dcalendar
DateMonthYear
01/01/202312023
02/01/202312023
03/01/202312023
04/01/202312023
05/01/202312023
06/01/202312023
07/01/202312023
08/01/202312023
09/01/202312023
10/01/202312023
11/01/202312023
12/01/202312023


OUPUT NEEDED:
I need to create a graphic that shows my Daily WIP (work in progress). Like this (I just need the visual, the table beside is just the data that genated it - but it would be a 'nice to have' to know all Issues in each day as a tooltip for example)
Daily Wip.jpg

Daily WIP is the number of Issues (every Issue is a 'key') that was being doing in that day. It will be cosider "being doing" all statuses diferent from Done and Backlog ('In Progress', 'Ready to Release'). The table above shows the Issues that are considered in each Day, for example:
The WIP, for Issue X-1, starts at '02/01/2023' (it was moved to 'in progress' status at 02/01/23) and ends at 05/01/2023 (it was moved to 'Done' the next day).

1 ACCEPTED SOLUTION
AlanFredes
Resolver IV
Resolver IV

Hi Pedro,

 

I had to merge the History table with a filter Issue table (Status=Done) to get the resolved date for each key. I did this in the Power Query editor.
This helped me create a DAX measure that could filter this New_History table and get the result you wanted.

Below is a link to the file I created with this new table and DAX measure.
Let me know if the join is not possible to do in your data due to too many rows and we can keep troubleshooting the issue.

 

AlanFredes_0-1688583552363.png


Link to File: DAX to create a Graphic (DAILY WIP) with Jira Software Database (help please).pbix

 

Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
AlanFredes
Resolver IV
Resolver IV

Hi Pedro,

 

I had to merge the History table with a filter Issue table (Status=Done) to get the resolved date for each key. I did this in the Power Query editor.
This helped me create a DAX measure that could filter this New_History table and get the result you wanted.

Below is a link to the file I created with this new table and DAX measure.
Let me know if the join is not possible to do in your data due to too many rows and we can keep troubleshooting the issue.

 

AlanFredes_0-1688583552363.png


Link to File: DAX to create a Graphic (DAILY WIP) with Jira Software Database (help please).pbix

 

Did I answer your question? Mark my post as a solution!

Wow! What a smart way to solve this problem.

However, I'm afraid this join could get me in trouble since my History table will have something like 1-2 millions rows... what do you think about that?

Could I remove the join and use 
"LOOKUPVALUE(Issues[Resolved Date], Issues[Key], 'New History'[Main_Key])" In place of (Issues.Resolved. Date) ?

It seems to work but i'd like double check with you... do you have any disclaimer about that? 

and again, thanks for helping me. 

New measure:

WIP Count =
VAR Now_Date = MAX(Dcalendar[Date])
RETURN
CALCULATE(
    DISTINCTCOUNTNOBLANK('New History'[Main_Key]),
    FILTER(
        'New History',
        'New History'[Status]<>"Backlog" && 'New History'[Status Date]<=Now_Date && OR(LOOKUPVALUE(Issues[Resolved Date], Issues[Key], 'New History'[Main_Key])>Now_Date,LOOKUPVALUE(Issues[Resolved Date], Issues[Key], 'New History'[Main_Key])=BLANK())
    )
)

 






Hi Pedro,

LOOKUPVALUE would work. NATRUALLEFTJOIN was also an option but brings in all the columns from the Issue table.
Note that by doing the join in DAX it will eat up visualization memory. which in Power BI premium is 6GB in P1 node (Link). You shouldn't have any issues as long as you are not pulling in too much data at the same time into the visualization. If you are just looking at 1 month at a time you should be good but if you start pulling in years of data you might see that the visualization will take time to render and if the worst case throw an out-of-memory error. Note that if your computer has more than 6GB of memory you will have no issues but once you publish it you will get a memory error in Power BI Service. I learned this the hard way.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors