Compare columns in pivot table and show rows

[Mukesh_s]

Hi, I am trying to compare the columns in a pivot table and show rows only if their is a difference between column values.

Below is the example pivot  

As their is a difference between the columns for the members 1016 and 1019 which are highlighted in the above image the output must show only these two rows in the pivot table.

Thanks

[E_Røse]

I am sorry, but did you try my measure or not? Because, when I entered the data in your table it returns exactly the table that you said you wanted.

I am not testing for missing values, I am testing If the only function returns null. But the following is perhaps more intuitive: Create a test by counting the number of distinct values in each row. If this returns something other than 1, then show the value. But you have to make sure that null or missing vales are also counted as a value. For null to be counted as a value you use coalesce(Value, 'something') and for missing to be counted as a value, you need to expand your table using the script I suggested before.

Something like

If (count(total <MemberNumber> distinct Coalesce(Value, 'null')<>1), Value)

or

If (count(total <MemberNumber> distinct Coalesce(Value, 'null')<>1), Coalesce(Value,'-'))

Or you could also go for a measure with aggr(nodistinct ...).

You need to include the script I suggested as well. Otherwise the measure may fail to show all the lines you want

And, could you please show me some respect and let me know if you tried the measures or not? And If you did, and it did not work, then can you show me an example where it does not return what you want.

[E_Røse]

Fyi. The previous comment was updated several times, check the measure from the updated version.

[henrikalmen]

@E_Røse It's a clever solution, but I can't get it to work. I built a simple example just because I was curious to see if you had gotten it right. I'm not sure why I can't get it to work though, it is a clever suggestion.

The result of the count will be 1 for the rows that should be included (because there is one distinct value over both columns), and 2 for the rows that should not be included (because there are two different values in the two columns). It works when using only the inner count (removing the outer if statement), and by that I mean that the count result (1 or 2) is displayed for each MemberNumber and it is the same in both columns (no null values).

But the if-statement just won't work, even though it looks to me as if it should work when just reading the code. It returns either the Value or [null] (not the string 'null' but the "null value"). Right now I don't understand why (but I'm likely in for the "oh no, of course, how stupid I am" revelation when I do). Adding a second parameter to the if-statement - a value to display in the case that the statement is not fulfilled - doesn't work either.

I also tried, for debugging purposes, to write the simple measure expression Coalesce(Value, 'null') and it results in nulls (instead of the string 'null') where data is missing. It seems it doesn't work in a pivot table.

Here is my test data script:

pivotdata:
load * inline [
MemberNumber, L_FRP, Value
1002, 2022-09-30, B
1002, 2022-06-30, B
1017, 2022-09-30, A
1017, 2022-06-30, A
1016, 2022-09-30, B
1019, 2022-06-30, B
1020, 2022-09-30, A
1020, 2022-06-30, C
];


tmp: noconcatenate Load distinct MemberNumber Resident pivotdata;

outer join(tmp) Load distinct L_FRP Resident pivotdata;

Outer Join(pivotdata) Load * resident tmp;

Drop table tmp;

[E_Røse]

What comes below here has been edited multiple times:
------------------------------------
@henrikalmen  I did the last suggestion  from my mobile without testing, but i think the second measure from the post today would work? 

I can still get one of the measures I suggested yesterday to work (upper left corner in image below). I will share my app with both versions. Actually, I cant remember why I put an 'Only' within the second coalesce.. It works without.

This is what it looks like in SaaS. (I copied your load script)

What could be a problem though, is if there are rows with diffferent values for the same date and member number.

Last edit at 23:43

[E_Røse]

Regarding "What could be a problem though, is if there are rows with diffferent values for the same date and member number."

This could probably be solved by adding a  concat around the Value inside the coalesce-functions.

[henrikalmen]

Ah, yes you're right @E_Røse. Looking again at my test app there was a weird additional thing going on in  my pivot table, I'm blaming it was too late in the evening for me. 😉 And I also forgot to uncheck "include zero values" under "Data handling" for the pivot table...

Nicely done! @Mukesh_s should really explore this because if he has given us all necessary facts about his setup he should be able to see that you have given him the solution he needs.

[E_Røse]

@Mukesh_s, did it work?