Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have connected my Power BI Desktop to an SSAS Server (2014). The data model has a date aggregation dimension with the following values (MAT, 3M, 6M, None, YTD). I'd like to create the following visual:
* Rows: Product Category
* Columns: Date Aggregation (3M, 6M, MAT, YTD (basically everything except of None))
* Values: Sales
Additionally I have added a filter on Month (selected on Aug 15).
The visual is shown as expected, but I'm not able to get rid of none. If I apply a filter on the Date Aggregation table (as visual level filter or page level filter ), I can just select one specific date aggregation, but not select several or unselect none.
Also if I add a slicer on Date Aggregation, the slicer selection controls are not shown.
Any ideas why this is not working and what I can do to achieve this. As mentioned, I have set a filter on a specific month, so that shouldn't be the reason.
Thanks,
André
Solved! Go to Solution.
Unfortunately I do not know the specification of the cube. However I installed the latest version of Power BI Desktop and now I can set the filter as planned by marking the appropiate Date aggregations by holding CTRL. So my primary request has been solved.
Thanks
Hi there,
I have make formula for
MAT
MAT=if(COUNTROWS(DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-12,MONTH))=12,CALCULATE(sum('Page1'[sales]),DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-12,MONTH),all(DimDate)),0)
if you would like to 3M, 6M just change -12 to the period you want.
YTD
YTD=CALCULATE(sum('Page1;[SALES]),DATESYTD(DimDate[Date]),all(DimDate))
Hope this answer work..
Hi there,
I have make formula for
MAT
MAT=if(COUNTROWS(DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-12,MONTH))=12,CALCULATE(sum('Page1'[sales]),DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-12,MONTH),all(DimDate)),0)
if you would like to 3M, 6M just change -12 to the period you want.
YTD
YTD=CALCULATE(sum('Page1;[SALES]),DATESYTD(DimDate[Date]),all(DimDate))
Hope this answer work..
Unfortunately I do not know the specification of the cube. However I installed the latest version of Power BI Desktop and now I can set the filter as planned by marking the appropiate Date aggregations by holding CTRL. So my primary request has been solved.
Thanks
Hi @Andwu,
How did you define the date aggregation dimension and values (MAT, 3M, 6M, None, YTD)? Please share some screenshots about the report if possible.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |