Forum Discussion
create custom table issue
hi there,
I would like to create a customer table in BI like using Excel, I am thinking to use {(1,CALCULATE(SUM(Sheet1[amt]),FILTER(ALLSELECTED(Sheet1),Sheet1[item]="1.sales"))),(2,xxx),(3,xxx)}, union function etc. I would like to create the table as screen shot below in Power BI, the row value maybe from measure or directly from data file. I am not sure if it is a good method to create data table by using formula like {1,2,3} dax function. If I created that datatable, it should be connected with slicer. I find a problem that when I create a datatable, the slicer is not working.
I created a dummy data model below, the table on right created manually, however, it is not connected with slicer. how to make a good customized datatable connected with slicer? actually, it is a very basic function in Excel, but seems to be very difficult in Power BI, thanks
Best regards
ER
Hi erihsehc,
Based on my assumption, you want a calculated table like this, right?
However, even we create a relationship between this calculated table and source table, the [Amount] value won't be changed according to slicer selection. Because [Amount] is initialized once we created the table. In other words, the values are static in a calculated table.
Why do you need to create such a custom table? In your scenario, if you want to display value conditionally depend on slicer, you'd better use measures. For example, add [item] from 'Sheet1' into matrix rows, add measure Sum Amount=SUM(Sheet1[amt]) into matrix values. An easier way is directly add [amt] into matrix Values and choose Sum aggregation for it.
Best regards,
Yuliana Gu
1 Reply
Hi erihsehc,
Based on my assumption, you want a calculated table like this, right?
However, even we create a relationship between this calculated table and source table, the [Amount] value won't be changed according to slicer selection. Because [Amount] is initialized once we created the table. In other words, the values are static in a calculated table.
Why do you need to create such a custom table? In your scenario, if you want to display value conditionally depend on slicer, you'd better use measures. For example, add [item] from 'Sheet1' into matrix rows, add measure Sum Amount=SUM(Sheet1[amt]) into matrix values. An easier way is directly add [amt] into matrix Values and choose Sum aggregation for it.
Best regards,
Yuliana Gu