measure with related & error condition

[Mattpar]

Hi,

I have two tables (Transactions and options) linked to each other with many to one relationship through "Change No" column. 

There are some items in my transactions table that do not have a matching value in options table. Therefore, when I use "options" column values from options table and Transaction_id from transactions table I get a rov with a blank cell for these items.

What I am trying to do is to call these blank row items a "No options" category.

I managed to do this by creating a calculated column in my transactions table, using;

Options = IF('transactions'[Variations/Base Scope]="Variations",
LOOKUPVALUE('Options'[Options],' options'[Change Number], 'transactions'[Change No], "NO OPTION"))

If I pull this calculated column to my rows in report view, I can see what I want.

Options        Count of Transaction_id 

No Option      7

Option-1        3

Option-2        3

However, my actual data set is quite large and instead of creating a calculated column I would like to solve this with a measure.

I tried using Calculate and Related functions together but I could not figure out how to enter the if, error and related column condition.

Appreciate for the help. 

Below are my sample tables;

Transactions table;

Options table :

model view

Report view - table

[v-diye-msft]

Hi @Mattpar 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

If the question still exists, please kindly share more details about your data, simple file/dummy pbix is preferable.

[Ashish_Mathur]

Hi,

The variations 2 table should be build from the transactions 2 table.  Make a copy of the transactions 2 table and remove all columns other than the Change No column (this will now be your variations 2 table ).  Remove duplicates from this column.  In another column of this table, enter option-1.  Now create a relationship between these 2 tables.

[d_gosbell]

So you could do this with a measure, BUT you would need to have a row in your options table for the "NO OPTIONS" value. This is because measures can change the values returned against an existing row, but they cannot generate new rows on the fly.

If you have a NO OPTIONS value in your table such a measure would look something like the following:

Measure = if (SELECTEDVALUE(Options[Option]) == "NO OPTIONS" ,CALCULATE(COUNTROWS(Transactions),ALL(Transactions), ISBLANK(('Options'[Option]))),
 if( ISBLANK( SELECTEDVALUE('Options'[Option]) ),blank(), COUNTROWS(Transactions)))

 Basically this is 2 IF statements, one checks if the current row is the "NO OPTIONS" value and returns the value for the blank, unmapped values, the second IF returns a blank for the blank options.