Calculated column does not work?

[C4YNelis]

Dear all,

I am currently trying to create a solution where I can dynamically fill one and the same matrix, based on several columns with criteria. All my columns (which all have a (text) value simular to either "check" or "not relevant") are in the same table. However, I wanted to create just one slicer, which shows all the desired filter options.

Everything goes well, except that in the final step, my calculated column remains blank, while I want to have it filled with either True of False. I have read different posts on different (but related) solutions and from what I gather and understand, it should be possible to do what I am trying to do. Am I wrong? What am I missing?

In order to do this, I have created a second table, which consists of just one (text) column, filled with just the unique criteria I need in my slicer. This table however is completely unrelated to my first table.

See for an example of the tables below:

example of table structure. Multiple fields with criteria and a separate table with all criteria.

The actual tables look like this:

Table with criteriaTable with Criterium 1 (also 2, 3, etc)Note that there is no relation between the tables.

Next, I created my slicer and filled it with the values from the table with the source criteria:

Example of the report with slicer. Also note the measure I created to "read" the slicer and store the value for later use.

The measure I created contains the formula to read the slicer, only when a single value is selected:

gefilterd scenario = If(CountRows('BOM scenario''s')=1;VALUES('BOM scenario''s'[Bom scenario's]);Blank())

Now my problem is that I wanted to use this measure to create a calculated column, based on whether the relevant criterium is met, resulting in a True or False. This calculated column would then be the basis for a visual level filter, thus creating a dynamically filled matrix.

As you can see in the printscreen above, I have two Card visuals showing the values for two measures respectively. The first is for: "gefiltered scenario" (which is the measure mentioned before). With the current slicerselection being: "Order > 90dgn", you can see that it returns exactly this value, which means it works as intended.

Next I have a different measure (now I am aware this can all be done much easier in one measure, but since I did not get it to work and to clarify the problem, I broke it up into small subsets):

Filter test = "Order > 90dgn" = [gefilterd scenario] 

This results in a True or False, intended to be the logical check of an if-then-else-construction, meant to fill a calculated column with either True or False. As you can see, the measure evaluates to True, which it is supposed to.

Finally (and this is where things go wrong), I want to fill the calculated column:

Show values = if([Filter test]=True();True();False())

However, the result (column type as boolean) is not what I should be:

resulting calculated column does not fill appropriate.

No matter what I try, the calculated column refuses to fill as desired. If I use a text result rather than a boolean, the column stays blank. What am I missing here?

p.s. Right now, this uses just one criterium, however, when this works, naturally the other columns with criteria will be added to the formula, but that would be a small change. I figure that if one works, more than one will work too.

Thank you for your help,

Best regards,

Niels

[v-huizhn-msft]

Hi @C4YNelis,

>>Finally (and this is where things go wrong), I want to fill the calculated column.
Where did you create the calculated column? In your scenario, there are several tables. From the screenshot, I am not reproduce the same scenario.

Based on my understanding, I try to create sample data shown in the screenshot below and test it as follows.

First I create a slicer including the Bom scenario’s field. Create a measure like yours, create a card used to display the result.

gefilterd scenario = If(CountRows(Test4)=1,VALUES(Test4[Bom scenario's]),Blank())

Finally, create a calculated using the following formula and get the desired result.

result = IF(Test4[Bom scenario's]="Order>90dgn",TRUE(),FALSE())

In addition, if you have multiple values in the field used in slicer like the first screenshot. Then it will return blank even though I use the same measure. Because the CountRows(Table13) is not 1. So it return the wrong result.

gefilterd scenario111 = If(CountRows(Table13)=1,VALUES(Table13[Bom scenario's]),Blank())

If this is not what you want, could you please post the .PBIX for better analysis?

Best Regards,
Angelia

[C4YNelis]

Hi Angelia,

first of all, thank you for your reply and my best wishes for 2017!

Forgive me my late reply, I had a couple of days off for the holiday.

I have multiple tables indeed, but I really only have one primary table (let's call this my "source" table). This table needs to be filtered selectively based on the chosen scenario (that's what I use the matrix for together with the calculated column (true/false) as input for a visuals filter.

Besides this source table, I have created a second table consisting of only one column, used to fill the necessary slicer. I'll call this the "helper" table.

The calculated column needs to be placed in the source table. In your example you created the calculated column in the helper table, however, that is no use unfortunately in my scenario.

You're right to say that it will not work when I would select more than one option in the slicer, however, in my report that would make no sense to begin with. This is specifically a report designed to filter out a specific workload based on the selected criteria.

Unfortunately I cannot just share the .PBIX due to the sensitive nature of the data. However, if I need to clarify anything, please ask.

I hope this clarifies the problem a bit?

Thanks again and best regards,

Niels

[C4YNelis]

Hi @v-huizhn-msft

I may not be able to post the original .pbix file, but I have created a new fictive .pbix file that generates the same problem. Please find it on dropbox as I cannot upload it here: Sample PBIX file

What I have done in this file to further downdrill to the actual problem (I hope):

I've created the same type of measure as before, reading a single value from the slicer:

Selected_Slicervalue = if(CountRows('Slicer_Criteria')=1;Values(Slicer_Criteria[Criterium]);Blank()) 

To verify, I have a card to show the value.

I then tried creating multiple calculated columns to see how Power BI reacts:

1. First I have created the calculated column as I would ideally like to use it (eventually):

Row_Selected_1st_Attempt = If([Selected_Slicervalue]="18+"||[Selected_Slicervalue]="18-";if([Selected_Slicervalue]=Source_Table[Age];True;False);If([Selected_Slicervalue]="Male"||[Selected_Slicervalue]="Female";if([Selected_Slicervalue]=Source_Table[Sex];True;False);False))

This obviously did not work, the calculated column is completely filled with the value False. The printscreen below shows this (please note that the columns are not shown entirely in the same order due to an extra column I added afterwards).

2. Next I tried just to match one value (specifically testing with "18+"), keep things simple, right? As expected, this didn't work either.

Row_Selected_2nd_Attempt = If([Selected_Slicervalue]=Source_Table[Age];True;False)

3. I then thought I'd try a general logical test using an additional measure, testing for "18+" to be true or false and fill the column based on this measure being true, eliminating the possibility that this would result in false, rather than true. Again, not the desired result.

The measure (which evaluates to True, when selecting "18+", as can be seen from the card I added to the report):

Selected_is_True = [Selected_Slicervalue]="18+" 

The column:

Row_Selected_3rd_Attempt = If([Selected_is_True]=TRUE();True;False)

Also, I don't understand why sometimes it persists in adding brackets when typing True and sometimes it just doesn't seem to care. The effect seems the same though.

4. Then I thought, let's see how the column behaves when I simply test for this exact value (basically, the same as how I used the measure).

Row_Selected_4th_Attempt = If([Selected_Slicervalue]="18+";True();FALSE())

Still, the whole column remains false, while I would expect it to be true.

5. Not giving up, I tried a different way of just forcing it to be true (there has to be some way?):

Row_Selected_5th_Attempt = If([Selected_Slicervalue]=[Selected_Slicervalue];True;False)

Now, slightly to my surprise, this time it did evaluate to true, however, the results naturally are useless this way.

6. And finally, what I also wanted to test, is whether I didn't make any mistake in my assumption that I could calculate the column based on the value in the column: "Age". Though I already knew this would work, I just wanted to be thorough.

Row_Selected_6th_Attempt = If(Source_Table[Age]="18+";True;False)

Now, here are the columns as they evaluated:

The results. Note that the columns are not entirely in order

I forgot to mention that again all calculated columns and measures are calculated in the Source_Table, which differs from the table with the Slicer_Criteria.

From what I've seen, my best guess is that it is simply not possible to use this value in a calculation because of the unrelated tables, however, I am relatively new to Power BI and I cannot really think of a reason why this would be so.

Hopefully someone does? Alternatively, if anyone knows of a different way of filtering the situation as desired..., I'm keeping all options open at this point.

Thank you again.

Best regards,

Niels

[C4YNelis]

Unfortunately I have still not managed to solve this problem. I can only conclude that it is simply not possible to create a calculated column based on a measure that (partly) uses an unrelated column from a different table. I don't really understand why this would pose an issue though, however, I have done a couple of simular tests and they all show the same results.

I have been able to solve my immediate problem via an alternative route, creating a dynamic solution via M Query, reading and splitting a column to their unique values mapped to their occurences, both individual and combined, with two different functions and an iterative loop.

If anyone is interested in the final solution, just let me know and I'll try to explain.

cheers,

Niels