Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Team,
Once again i need some help with Power Bi. I have 2 connected tables to QuickBase, one of them contains information of number of cases in previous 3 months, second table contains all cases since beggining of the year. I want to create a single chart where i can show case count for previous 3 months based on 1st table, and case count for current month based on 2nd table. My idea is to create a summary table which will hold values that chart will use to visualise data. To visualise it better:
1st Table
Month End | Product | LoB | New | Closed |
31-01-2020 | Box | Sales | 3 | 26 |
31-01-2020 | Shapes | Market | 6 | 11 |
29-02-2020 | Widget | Net | 1 | 2 |
31-03-2020 | Can | Food | 17 | 7 |
2nd Table
Date Case Created | Product | LoB | IsClosed | Case Owner |
01-02-2020 | Can | Food | FALSE | Tom Jerry |
28-03-2020 | Widget | Net | TRUE | Jack Sparrow |
05-04-2020 | Box | Sales | FALSE | Case Owner |
05-04-2020 | Shapes | Market | FALSE | Case Owner |
I decided to create 3rd table where type equals to Backlog for previous 3 months:
Month | Month# | Type |
January | 1 | Backlog |
February | 2 | Backlog |
March | 3 | Backlog |
April | 4 | |
May | 5 | |
June | 6 | |
July | 7 | |
August | 8 | |
September | 9 | |
October | 10 | |
November | 11 | |
December | 12 |
I extracted month name from [Month End] (1st table) and [Date Case Created] (2nd table) to new column in each table. So all tables have column "Month" with month names. And now what i am struggling with, how do i summarize it?
I am trying to build DAX function for 3rd table like this:
Column Name = If('3rd Table'[Type]="Backlog",Count('1st Table'[New] WHERE '1st Table'[Month]='3rd Table'[Month]'),Count('2nd Table'[isClosed] WHERE '2nd Table'[Month]='3rd Table'[Month]'))
I know syntax is no correct, i just wanted to describe my approach for it.
Thanks in advance for any help.
Ksusser
I would skip the summary table and instead create a Date table using CALENDAR or CALENARAUTO. Link the Date columns together for these 3 tables. Then you can simply use default aggregations in your visual.
Thanks for response,
I am not sure if calendar table will work cuz my end goal is to create bar chart with certain conditions.
Where:
data from January, February and March will come from 1st table
but data for April will come from 2nd table
And this should always work for the previous 3 months and the current month.
I think summary table is a must in this case.
Regards,
Ksusser
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |