Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I create this new table:
Calculated tables are only calculated during data refresh, so they are not affected by any slicers or filters.
Depending on what you are trying to achieve it might be enough to simply add the Company Code and Business Unit columns and the Total measure to a table visual. That would update with any user changes to a slicer
Hiii, I put it in a calculated table as I want to use the Total to do further calculation. I am not sure how to directly use the visual output as input for other calculaiton? If you have any ideas, that would be great, good weekend.
You can create temporary calculated tables within measures, e.g. if you wanted to get the max total value when summarized by company you could do something like
Max Total Value =
var summaryTable = ADDCOLUMNS( SUMMARIZE('Table1'[Company Code],'Table1'[Business Unit]) ,"Sumup",'Table1'[Total])
return MAXX( summaryTable, [Sumup])
You can't use SUMMARIZECOLUMNS within a measure, so you have to use ADDCOLUMNS .. SUMMARIZE
I m rather new to dax, trying to follow your big idea and appreciated!!!!
The calculation I will be doing is: use "Company Code + Business Unit" as the key to match a rate in a second table (The table be like: Company Code| Business Unit | Rate| Matchkey), and use [Sumup] from temporary calculate table multiply the rate.
trying to write dax, but failed😅, would you further help me out on this
Measurev =
var stbl = ADDCOLUMNS(SUMMARIZE('Table1','Table1'[Company Code],'Table1'[Business Unit],"Sumup",'Table1'[Total]),
"Match",'Table1'[Company Code]&"+"&'Table1'[Business Unit],
"rate",RELATED('Table2'[Rate])
return (stbl[Total]*stbl[rate])
OK, I don't think you need a summary table at all for this.
If you have one-to-one or one-to-many relationship between Table1 and Table2, with Table 2 on the one-side, then you can try
Measurev =
SUMX( 'Table1', 'Table1'[Rate] * 'Table1'[Amt] * RELATED('Table2'[Rate]) )
If you don't have, and can't make, a relationship then you could use LOOKUPVALUE, e.g.
Measurev =
var currentCompany = SELECTEDVALUE('Table1'[Company Code])
var currentBusinessUnit = SELECTEDVALUE('Table1'[Business Unit])
var table2Rate = LOOKUPVALUE( 'Table2'[Rate], 'Table2'[Company code], currentCompany,
'Table2'[Business unit], currentBusinessUnit)
return SUMX( 'Table1', 'Table1'[Rate] * 'Table2'[Amt] * table2Rate)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
82 | |
62 | |
61 | |
58 |
User | Count |
---|---|
159 | |
114 | |
100 | |
75 | |
65 |