DAX: Use measure outcome to populate calculated column without recalculating measure per row

Question

I have two tables in PowerBI. One called 'Fact_WorstInstance' contains rows of (Index,Instance). For example:

1,2
2,1
3,2

One called 'Fact_AllInstances' contains rows of (Index,Instance,Value). For example:

1,1,'Red'
1,2,'Green'
2,1,'Amber'
2,2,'Red'
2,3,'Brown'
3,1,'Green'
3,2,'Blue'

The first table is essentially a pointer to the worst entry in the second table for the given index (as categorised by some external system).

There is a slicer on which Indexes are visible to the user.

What I want to do is find the worst instance value for the highest visible Index in the 'Fact_WorstInstance' table, and then get all the Index and Value rows from the 'Fact_AllInstances' table for that Instance.

For example, if the slicer isnt filtering then (3,2) should be the active row from from the 'Fact_WorstInstance' table and this should be used to get Instance 2 from the 'Fact_AllInstances' table

1,2,'Green'
2,2,'Red'
3,2,'Blue'

from the 'Fact_AllInstances' table.

I tried to do this in many different ways, by creating a measure on the 'Fact_WorstInstance' which gives the highest visible row. And then use this measure to create a calculated column on the 'Fact_AllInstances', with 1 for worst and 0 for not worst. And then use this calculated column as a filter in PowerBI.

The measure itself gives the expected value. The problem I have is when the measure is used to create the calculated column, I cannot find a way to stop the Index being filtered based on the row of calculated column - and therefore the measure outcome changes for each row.

My measure:

Worst Entry = CALCULATE(FIRSTNONBLANK(Fact_WorstInstance[Instance],1),filter(ALLSELECTED(Fact_WorstInstance),Fact_WorstInstance[Index]=MAX(Fact_WorstInstance[Index])))

My column:

  WorstColumn = if(Fact_AllInstances[Instance]=[Worst Entry],1,0)

So instead of getting the output above, I get

1,2,'Green'
2,1,'Amber' --> because for Index 2, the measure gives index 1 as worst
3,2,'Blue'

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-02-20T20:02:37

This is a possible solution you might want to implement.

First of all, calculated columns are not affected by slicers/page filters, you will need to create a measure for that, so the way your are appraching the problem won't work.

Create an additional calculated table that holds unique instances values. In Power BI, Modeling tab there is a icon for creating a New Table, where you can use an expression to produce the table.

Use this expression:

IsntancesCalcTable = VALUES(Fact_WorstInstance[Instance])

Now you have a table called InstancesCalcTable in your model.

Drag the Instance column in the InstancesCalcTable and drop it in the Instance column of the Fact_WorstInstance, this will create a relationship between InstancesCalcTable and Fact_WorstInstance via Instance. A line between both tables will be drawn in the Relationships view, double click that line and you will see the Edit Relationship window.

Make sure it looks like this:

Then do the same for creating the relationship between InstancesCalcTable and Fact_AllInstances.

You will end with a model like this:

Then you can use Index column in the Fact_WorstInstance table, in a slicer and it will filter the Fact_AllInstances table to get only the instances selected.

However if you don't have any filter all rows in Fact_AllInstances will be shown.

DAX: Use measure outcome to populate calculated column without recalculating measure per row

1 Answers