Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi I have 3 columns in my fact table.
CountryKey DateKey TimeTaken
1 20170101 1600
2 20170101 800
3 20170101 400
4 20170101 10000
1 20170102 1200
My task is to calculate the average of the TimeTaken by Date (ignoring any records that are greater than 5000) and calculate the count of the records that have Timetaken greater than the average Time Taken...
I tried to write the query but I get a Circular dependency error...
Thanks in advance for the help.
Solved! Go to Solution.
How about this Measure? I think this is what you are trying to do...
Records Above Average by Date = IF ( HASONEVALUE ( 'Table'[DateKey] ), CALCULATE ( COUNTA ( 'Table'[DateKey] ), ALLEXCEPT ( 'Table', 'Table'[DateKey] ), 'Table'[TimeTaken] < 5000, FILTER ( 'Table', 'Table'[TimeTaken] > [Time by Date MEASURE 2] ) ) )
Here's the result...
OR...
Good Luck!
@a4apple Okay you want to ignore the Values over 5000 but you don't specify if you want to count those days or not?
So here are 2 Measures 1 will count the other won't... Hopefully the picture below will clarify what I mean...
MEASURE 1 - this will ignore the days over 5000 in numerator but will count in the denominator
Time by Date MEASURE = IF ( HASONEVALUE ( 'Table'[DateKey] ), DIVIDE ( CALCULATE ( SUM ( 'Table'[TimeTaken] ), ALLEXCEPT ( 'Table', 'Table'[DateKey] ), 'Table'[TimeTaken] < 5000 ), CALCULATE ( COUNTA ( 'Table'[DateKey] ), ALLEXCEPT ( 'Table', 'Table'[DateKey] ) ), 0 ) + 0, DIVIDE ( CALCULATE ( SUM ( 'Table'[TimeTaken] ), 'Table'[TimeTaken] < 5000 ), DISTINCTCOUNT ( 'Table'[DateKey] ), 0 ) )
MEASURE 2 - will ignore the days over 5000 in both numerator and denominator
Time by Date MEASURE 2 = IF ( HASONEVALUE ( 'Table'[DateKey] ), DIVIDE ( CALCULATE ( SUM ( 'Table'[TimeTaken] ), ALLEXCEPT ( 'Table', 'Table'[DateKey] ), 'Table'[TimeTaken] < 5000 ), CALCULATE ( COUNTA ( 'Table'[DateKey] ), ALLEXCEPT ( 'Table', 'Table'[DateKey] ), 'Table'[TimeTaken] < 5000 ), 0 ) + 0, DIVIDE ( CALCULATE ( SUM ( 'Table'[TimeTaken] ), 'Table'[TimeTaken] < 5000 ), CALCULATE ( DISTINCTCOUNT ( 'Table'[DateKey] ), 'Table'[TimeTaken] < 5000 ), 0 ) )
Here are the results with both Measures - take your pick!
Also note how each Measure Calculates in the Total Row!
Hope this helps.
Good Luck!
@Sean Thanks for the detailed reply.
Yes I don't want to count the records that have Timetaken greater than 5000
so I was expecting more like the average for 20170101 was 933.33
And I am also trying to find the count of records in my fact table that are greater than 933.33 for 20170101 , so my count will be 1 ( since I plan to ignore any thing with time taken more than 5000).
How about this Measure? I think this is what you are trying to do...
Records Above Average by Date = IF ( HASONEVALUE ( 'Table'[DateKey] ), CALCULATE ( COUNTA ( 'Table'[DateKey] ), ALLEXCEPT ( 'Table', 'Table'[DateKey] ), 'Table'[TimeTaken] < 5000, FILTER ( 'Table', 'Table'[TimeTaken] > [Time by Date MEASURE 2] ) ) )
Here's the result...
OR...
Good Luck!
@Sean Thanks for your help. What if I had to do the same in SSAS Tabular. Can I use the same?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |