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 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
Solved! Go to Solution.
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
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
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |