Forum Discussion
Supply True/False for Unique Combination in Rows
Hi,
I'm having trouble generating a formula using DAX that will supply me with a unique set of rows that have the same status. Example below:
I'm trying to fulfill a DAX formula (cannot be a measure) in power pivot editor tht supplies me with the "Boolean" field answers in the table below. The idea would be each Submittal would have to have the same Status for Boolean to be True, If a submittal has a different status then call it false. Help would be greatly appreciated as aggregations of this without a measure can get tricky.
Hi usergroup113231,
why do you want to do it in DAX? I think Power Query can do it better/easier.
In DAX, if you add an index column, you can do something like this:
if( COUNTROWS(FILTER('Table', EARLIER([Name]) = [Name] && EARLIER([Index])>[Index]))+1 = 1, 1, 0)Cheers,
John
4 Replies
in dax,
CalculatedColumn=CALCULATE(DISTINCTCOUNT(table[Status])=1,ALLEXCEPT(table,table[Submittal]))
Thank you much! Both formulas work like a charm!
Hi usergroup113231,
why do you want to do it in DAX? I think Power Query can do it better/easier.
In DAX, if you add an index column, you can do something like this:
if( COUNTROWS(FILTER('Table', EARLIER([Name]) = [Name] && EARLIER([Index])>[Index]))+1 = 1, 1, 0)Cheers,
JohnHi John!
Thank you for formula, reasoning is in our real model, we use a dax formula to show 20-30+ conditional iterations to supply the correct approval or denial, these change as business needs change and would be faster to apply in Dax quickly rather than in Power Query for every change and have to refresh.
