Merge (Union) or Measure or Pivot?

[kintera]

I have a dataset that has the following columns

Report_Date (09/27/19...)
Mtg1Late (TRUE, FALSE)
Mtg2Late (TRUE, FALSE)
Office (Macomb, Taylor)
Region (Region, Non_Region)

Logic
Late = if(or(Mtg1Late=TRUE, Mtg2Late=TRUE),1,0)

Area (from "Region") = if ysnRegion=TRUE, then "Region" else "Non_Region"

I want a Matrix to look like the below with Area (or Office and Region and Total) on the 1st column, and Report_Date across the top, with %Late as values

              9/24/19  9/25/19  9/26/19  9/27/19  
A            5             2            2             5
B            6             8            2             9
X            3             3           22            23
Y            9             3           2              25
Total      23           16         28            62

I also want to add a line on a linechart with dates on the horizontal, "A", "B", "X", "Y" and Total as series (or lines) and the values as the series values.

I implemented this as stated below, but *believe* there is an easier way. I'm going to try to clearly describe how I did it, then ask if there's a better way. Note, that I do not know DAX and AM trying to learn, but struggling.

I used above to make the first Query called qryTSCLate where I renamed "Office" as "Area", and added conditional column with above "Late" logic giving 1 for Late and 0 for not.

For the 2nd query (RegionLate2), I included pretty much the same columns, but added another conditional column titled Area (has logic "if ysnRegion=TRUE, then "Region" else "Non_Region") and again repeated the "Late" logic.

For the 3rd query (RegionTotal), I renamed the Tsc to Area, and changed all the values to "RegionTotal", again repeating the "Late" logic.

For the 4th and final query (AllLate), I created a Merge (Union) on the previous 3 queries. This, of course, tripled the number of records (to correctly show additional lines on the linechart.

I then used the below to calculate the %Late

%AllLate =
VAR Dt = SELECTEDVALUE(AllLate[dtmReportDate]) // Getting Current Date
Var Tsc =SELECTEDVALUE(AllLate[Area]) // Getting Current Office
VAR Ttl = CALCULATE(COUNT(AllLate[intLate]),FILTER(ALL(AllLate),AllLate[dtmReportDate]=Dt)) // Return  Total Number of Inputs for that office for particular date

%AllLate = 
VAR Dt = SELECTEDVALUE(AllLate[dtmReportDate]) // Getting Current Date
Var Tsc =SELECTEDVALUE(AllLate[Area]) // Getting Current Office
VAR Ttl = CALCULATE(COUNT(AllLate[intLate]),FILTER(ALL(AllLate),AllLate[dtmReportDate]=Dt)) // Return  Total Number of Inputs for that office for particular date
Var Cnt =CALCULATE(COUNT(AllLate[intLate]),FILTER(AllLate,AllLate[intLate]>0)) // Assuming Late flag is "1" or greater than Zero so filter out Count that are Late only

RETURN  

IF(ISBLANK(DIVIDE(Cnt,Ttl/3,0)),0,DIVIDE(Cnt,Ttl/3,0)) // Dividing Late Count with Total Count. 09/17/19 Given 3 datasets (TSC, Region, All) Ttl is 3x what it should be
//Change the Format of Measure to Percentage​))  

Given the Merge(Union) tripled the records (rows), I had to divide by 3 (shown above)

Then I used this to create the matrix and linechart.

So, does anyone have any thoughts? I *think* I could make the necessary columns and do a pivot, but also believe this could be done as measures.

Your thoughts are appreciated.

Lex

[dax]

Hi kintera,

I am not sure your query and data sample, so I can't reproduce your design. If possible,  could you please inform me more detailed informantion(such  as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kintera]

Hi again Zoe Zhi! ...and anyone that might be able to help

My sample data is here

https://1drv.ms/x/s!An0PAO7bbUt9gc0RR5pmqmoFl7uIzg?e=E7IGFU

1st Query
In my previous post, I referred to "Mtg1Late" and "Mtg1Late" (trying to make the communication easy). The data actually includes many "...Late" columns (7 in total). They all should be "OR" to result in "Late" ie (PrepareBasePlansLate=YES) OR (PlanReviewLate=YES) OR...

Then, we can show which "Tsc" is late (ie Taylor, Oakland, etc). This is one of the series (or rows), renamed to Area (see linechart below)

2nd Query
The other portion of the dataset is for ysnRegion. It results in new Area's (two) called "RegionPM" and "NonRegionPM". I created a new column Area, and set the Area as follows: if ysnRegion = TRUE, then Area = "RegionPM", else "Non-RegionPM". This is another one of the series (or rows) and doubles the number of records.

Both of these recordsets are used to count the number and show % of late jobs.

3rd Query
Again, I wanted to show the Total number of late jobs on the line charts.

So in my description above, the "A, B, X, Y, Total" are the lines on the linechart above.

i hope I've explained it well. 

I'm wondering if there is a more efficient way of doing it.

Thank you for any advise!