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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Combining Data from multiple tables on a shared timeline

Hi, 

 

Struggling with the following task. I have two tables (see image), both with timelines, and count of items per date. What I am trying to achieve is: 

 

1. Show data from both count columns on one shared timeline

2. Calculate data between two columns as follows: (#Items in Table1, in a given date)-(#Items in Table1, in a previous date)+(#Items in Table2, in a given date) 

3. Name the new calculated column

4. Display all 3 columns on a column+Line graph,

 

Thanks! 

 

(Example in the attached image)

Shared Calendar nd Calc.png

 

 

 

 

1 ACCEPTED SOLUTION

Hi @asaft,

 

Just created a model with two table with your data Backlog and Closed cases mades changes to distinct count on your measure and got the result below.

 

Count accumulate = 
VAR previous =
MIN ('Date Table'[Date]) - 1
VAR Ranking =
RANKX (
ALLSELECTED ('Date Table'[Date]);
CALCULATE (DISTINCTCOUNT(Backlog[Case Number]));
;
ASC
) //Calculates the ranking based on the period number in order to work with different period slicers
RETURN
DISTINCTCOUNT(Backlog[Case Number])
- CALCULATE (
DISTINCTCOUNT(Backlog[Case Number]);
FILTER (ALL(Backlog);Backlog[Snapshot Created Date] = Previous )
) //Calculation of variation to previous period
+ CALCULATE (
DISTINCTCOUNT('Closed Cases'[Case Number]);
FILTER(ALL('Closed Cases'); 'Closed Cases'[Date/Time Closed] = MIN('Date Table'[Date]))
)

Backlog.png

 

Believe everything is as needed. Please check it and tell me.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

13 REPLIES 13
MFelix
Super User
Super User

Hi @Anonymous,

 

Assuming you are using a calendar table and have that relates to your other two tables use this formula:

 

Count accumulate =
VAR previous =
    MIN ( 'Calendar'[Date] ) - 1
VAR Ranking =
    RANKX (
        ALLSELECTED ( 'Calendar'[Date] ),
        CALCULATE ( SUM ( Table1[Count Table 1] ) ),
        ,
        ASC
    ) //Calculates the ranking based on the period number in order to work with different period slicers
RETURN
    SUM ( Table1[Count Table 1] )
        - CALCULATE (
            SUM ( Table1[Count Table 1] ),
            FILTER ( ALL ( Table1 ), Table1[Snapshot Date] = Previous )
        ) //Calculation of variation to previous period
        + CALCULATE (
            SUM ( Table2[Count Table 2] ),
            FILTER ( ALL ( Table2 ), Table2[Closed Date] = MIN ( 'Calendar'[Date] ) )
        )

Preivous_Period.png

First Value is 53 since my data only starts at 03/06/2017

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix,

 

Thanks so much for your prompt reply. I have to admit that this is an advanced solution for me, so I would need also to understand the preliminary step of creating a calendar table and relating these two columns for it. Also, after having the calendar table, where and how do I use the query that you have created?

 

Thanks again! 

Anonymous
Not applicable

Hi again @MFelix

 

Using "Manage Relationship" I could relate the two calculated columns to a Date table as you can see below: 

 

New table.png

 

Now, when I look at the query editor I cannot find the new table that I have created.... How do I use the formula that you have provided then? 

 

Thanks

 

Hi @Anonymous,

 

You must create a measure in one of the tables with the code I provided.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix. I created a measure under one of the tables as follows: 

 

Count accumulate = VAR previous = MIN('Date Table'[Date])-1 VAR Ranking = RANKX (ALLSELECTED ('Date Table'[Date]), CALCULATE (SUM ('Open Cases Snapshot'[CaseNumber__c]) ),,ASC)

 

//'Date Table' is my calendar table and 'Open Cases Snapshot' is where the first column resides

 

When applying the formula I get this error 

 

The syntax for ')' is incorrect. (DAX((VAR previous = MIN('Date Table'[Date])-1 VAR Ranking = RANKX (ALLSELECTED ('Date Table'[Date]), CALCULATE (SUM ('Open Cases Snapshot'[CaseNumber__c]) ),,ASC))).

 

What am I doing wrong? 

 

Anonymous
Not applicable

Hi @MFelix

 

Sorry, the syntax was cut off. 

 

I modified your measure as follows: 

 

Count accumulate =
VAR previous =
MIN ('Date Table'[Date]) - 1
VAR Ranking =
RANKX (
ALLSELECTED ('Date Table'[Date]),
CALCULATE (SUM('Open Cases Snapshot'[CaseNumber__c])),
,
ASC
) //Calculates the ranking based on the period number in order to work with different period slicers
RETURN
SUM ('Open Cases Snapshot'[CaseNumber__c])
- CALCULATE (
SUM('Open Cases Snapshot'[CaseNumber__c]),
FILTER (ALL('Open Cases Snapshot'),'Open Cases Snapshot'[Snapshot Date] = Previous )
) //Calculation of variation to previous period
+ CALCULATE (
SUM('Case'[CaseNumber]),
FILTER(ALL('Case'), 'Case'[ClosedDate] = MIN('Date Table'[Date]))
)

 

But the results I get look like that: 

 

Calc.png

 

Any idea?

Anonymous
Not applicable

Hi @MFelix

 

Sorry, the syntax was cut off. 

 

I modified your measure as follows: 

 

Count accumulate =
VAR previous =
MIN ('Date Table'[Date]) - 1
VAR Ranking =
RANKX (
ALLSELECTED ('Date Table'[Date]),
CALCULATE (SUM('Open Cases Snapshot'[CaseNumber__c])),
,
ASC
) //Calculates the ranking based on the period number in order to work with different period slicers
RETURN
SUM ('Open Cases Snapshot'[CaseNumber__c])
- CALCULATE (
SUM('Open Cases Snapshot'[CaseNumber__c]),
FILTER (ALL('Open Cases Snapshot'),'Open Cases Snapshot'[Snapshot Date] = Previous )
) //Calculation of variation to previous period
+ CALCULATE (
SUM('Case'[CaseNumber]),
FILTER(ALL('Case'), 'Case'[ClosedDate] = MIN('Date Table'[Date]))
)

 

But the results I get look like that: 

 

Calc.png

 

Any idea?

Hi @Anonymous,

 

Looking at your calculation I believe that the problems is that you are making the SUM('Case'[CaseNumber]) this will give you very big value if the case number as more than 5 number. You need to change this to a count or distinct count

 

I used the sum in my case because I didn't know how you were making the count if it was a column with that value or a measure.

 

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix,

 

My columns are bases on Columns, not on measures. I just replaced all "Sum"s with "distinctcount", and I am closer but not correct yet.

 

This is what I get now

 

calc1.png

 

This is the syntax: 

 

Count accumulate =
VAR previous =
MIN ('Date Table'[Date]) - 1
VAR Ranking =
RANKX (
ALLSELECTED ('Date Table'[Date]),
CALCULATE (DISTINCTCOUNT('Open Cases Snapshot'[CaseNumber__c])),
,
ASC
) //Calculates the ranking based on the period number in order to work with different period slicers
RETURN
DISTINCTCOUNT('Open Cases Snapshot'[CaseNumber__c])
- CALCULATE (
DISTINCTCOUNT('Open Cases Snapshot'[CaseNumber__c]),
FILTER (ALL('Open Cases Snapshot'),'Open Cases Snapshot'[Snapshot Date] = Previous )
) //Calculation of variation to previous period
+ CALCULATE (
DISTINCTCOUNT('Case'[CaseNumber]),
FILTER(ALL('Case'), 'Case'[ClosedDate] = MIN('Date Table'[Date]))
)

Hi @Anonymous,

 

I believe is that what is happening is that you are getting the total values of the Closed cases, but not sure can you possible share some sample data?

 

Regards,

Mfelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix,

 

What if instead of this calculation, I will pull into the date table, another column with the number of cases opened on each date. It would serve the same purpose. Just that not sure this is logically possible. The Open date and the close date are on the same table. 

 

What would be the best way to privately share with you a sample data?

 

Thanks for your persistence (-:

 

Asaf

Hi @asaft,

 

Just created a model with two table with your data Backlog and Closed cases mades changes to distinct count on your measure and got the result below.

 

Count accumulate = 
VAR previous =
MIN ('Date Table'[Date]) - 1
VAR Ranking =
RANKX (
ALLSELECTED ('Date Table'[Date]);
CALCULATE (DISTINCTCOUNT(Backlog[Case Number]));
;
ASC
) //Calculates the ranking based on the period number in order to work with different period slicers
RETURN
DISTINCTCOUNT(Backlog[Case Number])
- CALCULATE (
DISTINCTCOUNT(Backlog[Case Number]);
FILTER (ALL(Backlog);Backlog[Snapshot Created Date] = Previous )
) //Calculation of variation to previous period
+ CALCULATE (
DISTINCTCOUNT('Closed Cases'[Case Number]);
FILTER(ALL('Closed Cases'); 'Closed Cases'[Date/Time Closed] = MIN('Date Table'[Date]))
)

Backlog.png

 

Believe everything is as needed. Please check it and tell me.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks so much @MFelix!

 

This has been really helpful!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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