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.
Hi,
I have a datatable with daily about 2,000,000 records and need to keep the records of 32 consecutive dates in de Power BI data model.
The table is refreched every day: the earliest data disappears and the data of the most date is added.
I need to calculate for 5 different metrics, who contain either a 0 or a 1, the percentage of 1's compared to the total number of records, grouped by 3 different properties: Date, Type, Department.
In the same table there is extra detail data that will be shown in a table in the same Power PI.
F_Table:
PK
Date
Type
Department
Metric_01
Metric_02
Metric_03
Metric_04
Metric_05
Extra_Data_01
Extra_Data_02
Extra_Data_03
Extra_Data_04
Extra_Data_05
Extra_Data_06
Extra_Data_07
Extra_Data_08
Extra_Data_09
I like to make an aggregated table based on this table with group by Date, Type, Department,
Sum of Metric_01, Metric_02, Metric_03, Metric_04, Metric_05
And Count Line
Based on the aggregations a line graph is to be made for the 5 etric fields percentages per date
How do I make this aggregate Table.
The original table is imported (no Direct Query)
R.W.
Thanks
Solved! Go to Solution.
Refer, if these can help
https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table
https://docs.microsoft.com/en-us/power-bi/desktop-aggregations
Hi @Anonymous ,
Please check if this is what you want:
1. Enter another table.
2. Create a measure like so:
Measure =
SWITCH (
TRUE (),
SELECTEDVALUE ( Metric[Metric] ) = "Metric_01", DIVIDE (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Metric_01] = 1
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
),
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)
),
SELECTEDVALUE ( Metric[Metric] ) = "Metric_02", DIVIDE (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Metric_02] = 1
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
),
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)
),
SELECTEDVALUE ( Metric[Metric] ) = "Metric_03", DIVIDE (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Metric_03] = 1
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
),
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)
),
SELECTEDVALUE ( Metric[Metric] ) = "Metric_04", DIVIDE (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Metric_04] = 1
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
),
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)
),
SELECTEDVALUE ( Metric[Metric] ) = "Metric_05", DIVIDE (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Metric_05] = 1
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
),
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)
)
)
3. Create a line chart.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks,
This was the sollution I already had more or less.
I wanted to use an aggregate table, so that on the moment we start using millions of records per date the measure wouldn't take too long.
R.W.
Refer, if these can help
https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table
https://docs.microsoft.com/en-us/power-bi/desktop-aggregations
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 |
---|---|
112 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |