exists in same table

[Anonymous]

hi, i'm new to Power BI...

not sure if i need a calculated column or a measure.

I want to check for each row if another row exists where value in column A is the same as this row and value in column B is 1.

so basically I would have a new boolean column.

or, optionally to have a new table with distinct columns A, B and the new bool column.

how should i do that in DAX and sould it be a column or a measure?

thanks

[PattemManohar]

@Anonymous  You can use a conditional column to flag row alreayd exists or not. But it will be great if you can post some sample data and expected output to suggest an accurate solution.

[Anonymous]

thank you @PattemManohar !

lets say i have the following data:  (ignore column D, it's only to show that data is distinct)

(of course real data is much bigger)

I need a report (or a graph) that will count distinct countries for each president that have at least one true value in column C.  and a count of countries without any true value.

so the report would be like that:

I guess there are a few ways to do that, but i'm not sure how...

Thanks a lot

I know my original question didn't mention all that   I was just trying to simplify

[PattemManohar]

@Anonymous  Please follow below steps:

Add a new column in the source table as below. This is to flag all the states as True if it contains atleast one True value.

CheckFlag = 
VAR _CurrState = Test191Lkp[State]
VAR _CurrPresident = Test191Lkp[President]
VAR _Result = LOOKUPVALUE(Test191Lkp[Flag],Test191Lkp[State],_CurrState,Test191Lkp[President],_CurrPresident,Test191Lkp[Flag],TRUE)
RETURN IF(ISBLANK(_Result),"F","T")

The result will now looks like..

Now create a new table as below which will use the above table as input.

Test191Out = 
VAR _True = SUMMARIZE(FILTER(Test191Lkp,Test191Lkp[CheckFlag]="T"),Test191Lkp[President],"TrueCount",DISTINCTCOUNT(Test191Lkp[State]),"Type","True")
VAR _False = SUMMARIZE(FILTER(Test191Lkp,Test191Lkp[CheckFlag]="F"),Test191Lkp[President],"FalseCount",DISTINCTCOUNT(Test191Lkp[State]),"Type","False")
RETURN UNION(_True,_False)

The output will be 

Now you can see the Matrix visual to represent this data in your required fashion.

[v-yuta-msft · Accepted solution]

@Anonymous ,

Create two measures using DAX below:

count with true = CALCULATE(DISTINCTCOUNT('Table'[A]), FILTER(ALLEXCEPT('Table', 'Table'[B]), 'Table'[C] = TRUE()))

count with false = CALCULATE(DISTINCTCOUNT('Table'[A]), FILTER(ALLEXCEPT('Table', 'Table'[B]), 'Table'[C] = FALSE()))

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@PattemManohar and @v-yuta-msft great thanks!!

(i think i'm missing the accept bottun... where is it?)

found it