topic Re: True/False column after evaluating a set of rows in DAX Commands and Tips

True/False column after evaluating a set of rows

Anonymous — Wed, 03 Aug 2022 15:24:47 GMT

Hi Everyone,

I need to create a Column in DAX to create the Bold Column in below Table. It would evaluate if each row# has a True Match in "Match?" Column, then new column will be True, otherwise False

Row#	Attribute	ValueBase	ValueCompare	Match?	All Values in same Row# Match?
1	1	A	A	True	False
1	2	A	B	False	False
1	3	B	B	True	False
2	1	A	A	True	True
3	1	B	B	True	False
3	2	C	A	False	False
4	1	A	A	True	True
4	2	A	A	True	True
4	3	C	C	True	True
4	4	B	B	True	True

Thanks

Re: True/False column after evaluating a set of rows

SpartaBI — Wed, 03 Aug 2022 15:42:50 GMT

Anonymous
solution file: True False column after evaluating a set of rows 2022-08-03.pbix

Write this calculated column:

Result Column = VAR _row = 'Table'[Row#] VAR _values = CALCULATE( DISTINCTCOUNT('Table'[Match?]), 'Table'[Row#] = _row, REMOVEFILTERS('Table') ) VAR _result = IF(_values = 1, TRUE(), FALSE() ) RETURN _result

Showcase Report – Contoso By SpartaBI

Re: True/False column after evaluating a set of rows

Anonymous — Wed, 03 Aug 2022 16:03:47 GMT

Hi SpartaBI ,

Thanks for your support on this. When I try your solution in my sample file/data I posted earlier it works perfectly. However, when I try this in my dataset (which it has the same structure/logic than the sample) for some reason is not giving the same output. Ive attached a portion of the dataset with both the sample data and this one with the solution.

SamplePBIX

Which could be the issue? When I read the logic behind your column I see that the output should be correct for my dataset as it is in the sample data.

Thanks,

Re: True/False column after evaluating a set of rows

SpartaBI — Wed, 03 Aug 2022 16:09:08 GMT

Anonymous if all columns for row number are false, you want to get false?
Cause that's not what I did but I will add this logic if that is what you meant.

Re: True/False column after evaluating a set of rows

SpartaBI — Wed, 03 Aug 2022 16:11:48 GMT

Anonymous like this?

Result Column = VAR _row = 'Table (2)'[RowNumber] VAR _test_for_true = 'Table (2)'[Is Predecessor Plan inside External Dependencies] VAR _values = CALCULATE( DISTINCTCOUNT('Table (2)'[Is Predecessor Plan inside External Dependencies]), 'Table (2)'[RowNumber] = _row, REMOVEFILTERS('Table (2)') ) VAR _result = IF(_values = 1 && _test_for_true, TRUE(), FALSE() ) RETURN _result

Showcase Report – Contoso By SpartaBI

Re: True/False column after evaluating a set of rows

Anonymous — Wed, 03 Aug 2022 16:13:01 GMT

Hi Sparta,

The outcomes should be:

If all Row# are False, then False
If some Row# are False and others are True, then False
If all Row# are True, then True

Re: True/False column after evaluating a set of rows

Anonymous — Wed, 03 Aug 2022 16:17:16 GMT

The test for true was the key! Ty!

Re: True/False column after evaluating a set of rows

SpartaBI — Wed, 03 Aug 2022 16:18:38 GMT

my pleasure 🙂
P.S. Kudos are always welcome 😀
Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

Re: True/False column after evaluating a set of rows

SahityaYeruband — Wed, 28 Feb 2024 06:59:57 GMT

Hi Sparta,
Can you please breakdown how - _test_for_true

is working?