This is an extension to an earlier issue on a different forum. ( https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-reference-columns-in-a-summarised-table/m-p/1911909#M41327 )
I have a table that has many columns, but for the purposes of this example looks like this (call it Order Table)
| Order ID | Item Code |
| O-001 | I-001 |
| O-001 | I-002 |
| O-001 | I-004 |
| O-002 | I-001 |
| O-002 | I-003 |
| O-003 | I-002 |
| O-003 | I-003 |
| O-004 | I-001 |
In my measure I create a Summarized table which tells me how many times an Item Code occurs in the above table. Effectively how many orders are using that Item Code. So based on the above it would look like this;
| Item Code | Occurs |
| I-001 | 3 |
| I-002 | 2 |
| I-003 | 2 |
| I-004 | 1 |
The above is therefore a virtual table in my Measure on the Order Table. The DAX to create the virtual Table is as follows
VAR ItemTable= SUMMARIZE(ALLSELECTED(OrderTable), OrderTable[Item Code], "Occurs", DISTINCTCOUNT(OrderTable[Order Id]))
What I want to do in my Measure now is access this virtual table to find the 'Occurs' value for the Item Code in the current context. The context being based upon OrderTable because that is where the measure is.
The long term intention is to create a measure that allows me to build a Pareto Graph, but I think my strategy is wrong because the data for the graph is largely in the virtual table. My virtual table just needs a cumulative sum of the Occurs column, built in descending order of that column. I think I need to create another base table, probably using Power Query, then use that new table to make my chart.
I would appreciate some advice / guidance please.
TIA
Hi Steve,
To sort that table, you can try the SAMPLE function:
SAMPLE(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])
for <n_value> value, you can use:
DISTINCTCOUNT(OrderTable[Item Code])
But, if you add a pivot table from OrderTable and add the Item Code into rows section, and any field into Values section as a Count,
this will return what you have in the virtual table without any measures needed, not sure what you are trying to do.
