Calculated column does not work?

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